aggregate sql - 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

aggregate sql

Author

Topic

zoe2003
Starting Member

17 Posts

Posted - 2012-05-17 : 09:31:38

Hello all,

I need your help please.
I have this table:

date user_id sum
1/1/2011 1 5
1/1/2011 2 0
1/1/2011 3 3
1/2/2011 1 2
1/2/2011 2 4
1/2/2011 3 1
1/3/2011 1 0
1/3/2011 2 2
1/3/2011 3 2

I need a query that will give me a cumulative values (for the sum column), such as:

date user_id sum
1/1/2011 1 5
1/1/2011 2 0
1/1/2011 3 3
1/2/2011 1 7
1/2/2011 2 4
1/2/2011 3 4
1/3/2011 1 7
1/3/2011 2 6
1/3/2011 3 6

for each date&user_id it will aggregate the sum column from the beginning to this date.
example: user_id=1 & date=1/3/2011 => sum=5+2+0=7.

Thanks,
Z.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-17 : 09:51:11

[code]
select t.date, t.user_id, t.sum, c.cumm
from this_table t
outer apply
(
select cumm = sum(x.sum)
from this_table x
where x.user_id = t.user_id
and x.date <= t.date
) c
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources