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!

Answer 1

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.

Answer 2

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?)