A query to accumulate a value - 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

Transact-SQL (2000)

A query to accumulate a value

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2005-07-28 : 11:52:40

Can you accumulate a value in a singe query, so that each row has the sum of all rows "above" it? For example, assume my table is;

Name Score AccScore
----- ----- --------
Abbot 10 0
Barns 5 0
Cabot 12 0

And I want this:

Name Score AccScore
----- ----- --------
Abbot 10 10
Barns 5 15
Cabot 12 27

I always end up writing a program in VB to do this.

Dom

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-28 : 12:30:40

using cursors you can do this easily without VB

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 12:47:43

Dunno if this would work - cursor will be slow


SELECT A.name, A.score, B.AccScore
FROM MyTable A
     JOIN 
     (
         SELECT B.Name, [AccScore] = SUM(B.Score)
         FROM MyTable B
         WHERE B.Name <= A.Name
         GROUP BY B.Name
     ) B
         ON B.Name = A.Name
ORDER BY A.Name

Kristen

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-28 : 13:08:04

yes, this should work faster

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-28 : 13:10:46

this provides an overview of different techniques:
http://www.sqlteam.com/item.asp?ItemID=3856

Go with the flow & have fun! Else fight the flow