Cumulative Sum Update - 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

Cumulative Sum Update

Author

Topic

mkdlmr
Starting Member

21 Posts

Posted - 2013-12-18 : 21:21:38

How can I update a table to have a running cumulative sum?

For Example:

Update Table1 Set Cumulative_Sum = Row_Sum + Previous_Row_Sum

It should look something like this:

Row_Sum Cumulative_Sum
1 1
2 3
3 6
4 10

Thanks,
Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-18 : 21:52:10

[code]
update t
set Cumuative_Sum = c.Cumuative_Sum
from Table1 t
cross apply
(
select Cumuative_Sum = sum(Row_Sum)
from Table1 x
where x.Row_Sum <= t.Row_Sum
) c
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mkdlmr
Starting Member

21 Posts

Posted - 2013-12-18 : 22:08:49

I don't quite fully understand . . . it appears that c is never associated with a table?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-18 : 22:12:33

c is a alias name for the CROSS APPLY result or "derived table"


update	t
set	Cumuative_Sum = c.Cumuative_Sum
from	Table1 t
	cross apply
	(
		select	Cumuative_Sum = sum(Row_Sum)
		from	Table1 x
		where	x.Row_Sum	<= t.Row_Sum
	) c

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 03:23:45

if its SQL 2012. you could simply do this


SELECT Row_Sum,SUM(Row_Sum) OVER (ORDER BY Row_Sum)
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mkdlmr
Starting Member

21 Posts

Posted - 2013-12-19 : 09:05:44

I wish! It's SQL Server 2008.

quote:
Originally posted by visakh16

if its SQL 2012. you could simply do this

SELECT Row_Sum,SUM(Row_Sum) OVER (ORDER BY Row_Sum)
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mkdlmr
Starting Member

21 Posts

Posted - 2013-12-19 : 13:30:18

Thanks! I got it working! :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources