SQL Server Multiple Running Totals

Question

I have a table like this

UserID  Score  Date
5       6      2010-1-1
7       8      2010-1-2
5       4      2010-1-3
6       3      2010-1-4
7       4      2010-1-5
6       1      2010-1-6

I would like to get a table like this

UserID  Score  RunningTotal Date
5       6      6            2010-1-1
5       4      10           2010-1-3
6       3      3            2010-1-4
6       1      4            2010-1-6
7       8      8            2010-1-2
7       4      12           2010-1-5

Thanks!

see http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver — codeulike, Jan 23 '13 at 18:33

score 1 · Accepted Answer · answered May 11 '10 at 16:12

Unlike Oracle, PostgreSQL and even MySQL, SQL Server has no efficient way to calculate running totals.

If you have few scores per UserID, you can use this:

SELECT  userId,
        (
        SELECT  SUM(score)
        FROM    scores si
        WHERE   si.UserID = so.UserID
                AND si.rn <= so.rn
        )
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY UserID) AS rn
        FROM    scores
        ) so

, however, this will be very inefficient for larger tables.

For larger tables, you could benefit from using (God help me) a cursor.

thanks for this. couple of syntax errors but it was enough to set me on my way. the "si.userId = so.UserID" was exactly what I needed. — super9, May 12 '10 at 08:54

score 0 · Answer 2 · answered May 11 '10 at 16:15

Would something like this work for you...?

SELECT UserID, Score, 
   (SELECT SUM(Score) 
   FROM TableName innerTable 
   WHERE innerTable.UserID = outerTable.userID 
      AND innerTable.Date <= outerTable.date) AS RunningTotal
FROM TableName outerTable

This assumes, though, that a user cannot have more than one score per day. (What is your PK?)

SQL Server Multiple Running Totals

2 Answers2