sum column value with value of column in next row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sum column value with value of column in next row

Author

Topic

veronika.np
Starting Member

29 Posts

Posted - 2011-06-08 : 10:52:24

hi friends

i have a problem.i have a table and i want to value of one column in my table sum with value of this column in next row.(i want to sum value of column in each row sum with value of this column in next row).
how can i do this?
please help me.
thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-08 : 12:59:38

Can you please give example table, example data and wanted result?

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-08 : 13:00:58

Can you post the DDL for the tables and some sample data? Brett's blog here would give you some help on how to post: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Also, by definition, rows in a table are unordered. So you need to have some column or logic that would define the sort order - i.e., the rule on how to find the "next row".

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-08 : 13:01:21

Fred, do I see a pattern emerging here

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-08 : 13:14:23

Hm... we have seen other days haven't we?
Sometimes I am the loser and sometimes the other one is winning
(My translation of a german phrase, I hope it is clear to understand the joke)

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-08 : 13:27:45

You mean something like "Heads I win, tails you lose" (when flipping a coin to determine the winner).

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-08 : 13:29:12

quote:
Originally posted by sunitabeck

You mean something like "Heads I win, tails you lose" (when flipping a coin to determine the winner).

YES

No, you're never too old to Yak'n'Roll if you're too young to die.

veronika.np
Starting Member

29 Posts

Posted - 2011-06-08 : 13:30:22

quote:
Originally posted by sunitabeck

Can you post the DDL for the tables and some sample data? Brett's blog here would give you some help on how to post: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Also, by definition, rows in a table are unordered. So you need to have some column or logic that would define the sort order - i.e., the rule on how to find the "next row".

veronika.np
Starting Member

29 Posts

Posted - 2011-06-08 : 13:33:09

quote:
Originally posted by sunitabeck

Can you post the DDL for the tables and some sample data? Brett's blog here would give you some help on how to post: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Also, by definition, rows in a table are unordered. So you need to have some column or logic that would define the sort order - i.e., the rule on how to find the "next row".

CREATE TABLE mytable(Col1  varchar(20))


INSERT INTO mytable(Col1)

 select 10

INSERT INTO mytable(Col1)

select 20

INSERT INTO mytable(Col1)

 select 30

i want this

10
30
60

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-08 : 14:00:11

You can do it using the code shown below, but give special attention the part I have highlighted in red. There, I am ordering the rows in the table based on the numeric value in col1. I need to do that because, even though you inserted the data into the table in a particular order, SQL Server does not guarantee that the data will be stored or retrieved in that order. So you have to explicitly say how you want it ordered. In this example, I just ordered it by the value of col1, for lack of anything else to order it by. In your real problem, you should do the ordering exactly the way your business logic requires you to.


;WITH A(col1, rn) AS
(
   SELECT 
      CAST(Col1 AS INT),ROW_NUMBER() OVER (ORDER BY cast(col1 AS INT))
   FROM
      myTable
),
B(col1,sumCol,rn) AS
(
   SELECT a.col1,a.col1, rn FROM A WHERE rn = 1
   UNION ALL
   SELECT a.col1,b.sumCol + a.col1, a.rn
   FROM A INNER JOIN B ON a.rn = b.rn+1
)
SELECT col1,sumCol FROM B ORDER BY rn;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources