Inclusive sum with group by - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Inclusive sum with group by

Author

Topic

eliewadi
Starting Member

20 Posts

Posted - 2009-02-06 : 17:08:29

Hi I have the following data that i would like to aggregate using the sum function and grouping by date. I also want the sum of each group to include the sum of its previous group.

Here is an example

2009-02-06 100
2009-02-06 200
2009-02-12 1000

Desired result

2009-02-06 300
2009-02-12 1300

What i get todays is

2009-02-06 300
2009-02-12 1000

Any ideas? thank you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 18:08:59

I guess you are looking for With Rollup or CUBE.

eliewadi
Starting Member

20 Posts

Posted - 2009-02-06 : 21:30:16

the rollup will give me one summary record like this:

2009-02-06 300
2009-02-12 1000
NULL 1300

instead of

2009-02-06 300
2009-02-12 1300

If im not using it as i suppose to can you please show me how.

I also read that rollup will not be supported in future sql server releases.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 22:15:15

[code]--Create TV
Declare @T table
([Date] datetime,Amt int)

--Prepare Sample Data
Insert @T
Select '2009-02-06', 100 union all
Select '2009-02-06', 200 union all
Select '2009-02-12', 1000

--Roll SQL Wheel
Select [Date],(Select Sum(Amt) from @T Where [date]<=M.[date])as [Total]
from @T M
Group by [date]
Order by [date]

--Output
Date Total
2009-02-06 00:00:00.000 300
2009-02-12 00:00:00.000 1300[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 22:33:16

In SQL 2005:

Select Distinct T.[Date],M.Total
from @T T 
  OUTER APPLY
   (Select Sum(Amt)as Total from @T 
   Where [date]<= T.[date])M

eliewadi
Starting Member

20 Posts

Posted - 2009-02-09 : 22:54:22

Both queries seem to work for me. The outer apply is something i did not know about. thank you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-10 : 01:01:41

Thank you .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 02:21:33

quote:
Originally posted by eliewadi

Both queries seem to work for me. The outer apply is something i did not know about. thank you

see this too

http://www.databasejournal.com/features/mssql/article.php/3616286/Apply-Operator.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources