Cummulative SUM - 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 2000 Forums

SQL Server Development (2000)

Cummulative SUM

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-07-26 : 17:07:41

Hi,
I am trying to do a Cummulative Sum for the data "Amt" field column below.
Can anyone please guide me how to do it.

ID.............Amt
1...............30
1...............20
1...............5
2...............4
2...............10

I am trying to get a cummulative sum

ID.............Amt...............CummulativeSUM
1...............30 30
1...............20 50
1...............5 55
2...............4 4
2...............10 14

Also I want to order by the Amt column DESC group by ID.....

Will appreciate any help in this regard.

Raj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-26 : 17:32:55

Is there something in the table that specifies the order of the IDs? It's easy if you want 55 for each ID=1, but your result set indicates an ordering of the IDs. Please provide more info.

Tara Kizer

rkumar28
Starting Member

49 Posts

Posted - 2006-07-26 : 17:53:47

Hi,

I am just trying to do the cumulative Sum per ID AND NOT THE REGULAR SUM. In cummulative sum we add the previous value of the AMT value plus the current row value for a particular Id. The value resets for each new Id. So for example:

ID.............Amt...............CummulativeSUM
1...............30...................30 + 0 = 30 (Since this is a first record)
1...............20................... 30 + 20 = 50
1...............5....................30 + 20 + 5 = 55 or (50 + 5 = 55)
2...............4 4 ..................4 + 0 = 4 (Since this is a first record for Id2)
2...............10 ...................4 + 10 = 14

Raj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-26 : 17:55:30

SQL Server has no idea which ID is the first one though. So something in your table needs to specify this. There is no way to get 30, then 50, then 55 without knowing how to order them.

Tara Kizer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-26 : 20:55:02

Also, if possible do Running Total in Front end application

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources