Looping Statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Looping Statement

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-16 : 17:06:26

Hi,

I have a table with 4 columns and 10 rows. Column 1 is with dates, Column 2 and 3 are with Integers. In the last column, in each row I want to sum the values of all other rows in column 2, and 3 BUT up to that row number. In other words, in row 4 column 4, I should have sum (column2, row1+row2+row3, column 3, row1+row2+row3,).. the calculations for the rest of the rows in column 4 follow the same logic...do you know what shoud I use to get that? is it some sort of loop statement? ..thanks!

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-16 : 17:06:40

I use sql server 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-17 : 04:55:16

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-11-17 : 05:04:25

[code]

declare @t table(col1 int,col2 int)
insert into @t
select 1,2 union
select 3,4 union
select 6,7 union
select 5,10
;with cte
as
(
select *,row_number()over(order by col1) rid from @t
)

select col1,col2,col3 from cte t1
cross apply(select sum(col1+col2)col3 from cte t2 where t1.rid>=t2.rid)T
[/code]

PBUH

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-17 : 09:45:54

Thanks Sachin - I couldn't quite understand what you are doing though.

Madhivanan - here is the sample data and result:

Column 1 Column 2 Column 3 Column 4
2011-01-01 , 500 , 150 , =500+150
2011-02-01 , 500 150 , =2*500+2*150
2011-03-01 , 400 100 , =2*500+2*150+400+100
2011-04-01 400 100 , =2*500+2*150+2*400+2*100

Please advise! thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-17 : 10:13:44

This is called Running Total. Search for the same. You will find lot of related links

Madhivanan

Failing to plan is Planning to fail

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-11-17 : 11:12:23

THANKS much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources