Calculate a Running Total in SQL Server

Question

Imagine the following table (called TestTable):

id     somedate    somevalue
--     --------    ---------
45     01/Jan/09   3
23     08/Jan/09   5
12     02/Feb/09   0
77     14/Feb/09   7
39     20/Feb/09   34
33     02/Mar/09   6

I would like a query that returns a running total in date order, like:

id     somedate    somevalue  runningtotal
--     --------    ---------  ------------
45     01/Jan/09   3          3
23     08/Jan/09   5          8
12     02/Feb/09   0          8
77     14/Feb/09   7          15  
39     20/Feb/09   34         49
33     02/Mar/09   6          55

I know there are various ways of doing this in SQL Server 2000 / 2005 / 2008.

I am particularly interested in this sort of method that uses the aggregating-set-statement trick:

INSERT INTO @AnotherTbl(id, somedate, somevalue, runningtotal) 
   SELECT id, somedate, somevalue, null
   FROM TestTable
   ORDER BY somedate

DECLARE @RunningTotal int
SET @RunningTotal = 0

UPDATE @AnotherTbl
SET @RunningTotal = runningtotal = @RunningTotal + somevalue
FROM @AnotherTbl

... this is very efficient but I have heard there are issues around this because you can't necessarily guarantee that the UPDATE statement will process the rows in the correct order. Maybe we can get some definitive answers about that issue.

But maybe there are other ways that people can suggest?

edit: Now with a SqlFiddle with the setup and the 'update trick' example above

Answer 1

In SQL Server 2012 you can use SUM() with the OVER() clause.

select id,
       somedate,
       somevalue,
       sum(somevalue) over(order by somedate rows unbounded preceding) as runningtotal
from TestTable

SQL Fiddle

Answer 2

SELECT TOP 25   amount, 
    (SELECT SUM(amount) 
    FROM time_detail b 
    WHERE b.time_detail_id <= a.time_detail_id) AS Total FROM time_detail a

You can also use the ROW_NUMBER() function and a temp table to create an arbitrary column to use in the comparison on the inner SELECT statement.

Answer 3

If you are using Sql server 2008 R2 above. Then, It would be shortest way to do;

Select id
    ,somedate
    ,somevalue,
LAG(runningtotal) OVER (ORDER BY somedate) + somevalue AS runningtotal
From TestTable 

LAG is use to get previous row value. You can do google for more info.

[1]:

Answer 4

The following will produce the required results.

SELECT a.SomeDate,
       a.SomeValue,
       SUM(b.SomeValue) AS RunningTotal
FROM TestTable a
CROSS JOIN TestTable b
WHERE (b.SomeDate <= a.SomeDate) 
GROUP BY a.SomeDate,a.SomeValue
ORDER BY a.SomeDate,a.SomeValue

Having a clustered index on SomeDate will greatly improve the performance.

Answer 5

Use a correlated sub-query. Very simple, here you go:

SELECT 
somedate, 
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
GROUP BY somedate
ORDER BY somedate

The code might not be exactly correct, but I'm sure that the idea is.

The GROUP BY is in case a date appears more than once, you would only want to see it once in the result set.

If you don't mind seeing repeating dates, or you want to see the original value and id, then the following is what you want:

SELECT 
id,
somedate, 
somevalue,
(SELECT SUM(somevalue) FROM TestTable t2 WHERE t2.somedate<=t1.somedate) AS running_total
FROM TestTable t1
ORDER BY somedate

Answer 6

I believe a running total can be achieved using the simple INNER JOIN operation below.

SELECT
     ROW_NUMBER() OVER (ORDER BY SomeDate) AS OrderID
    ,rt.*
INTO
    #tmp
FROM
    (
        SELECT 45 AS ID, CAST('01-01-2009' AS DATETIME) AS SomeDate, 3 AS SomeValue
        UNION ALL
        SELECT 23, CAST('01-08-2009' AS DATETIME), 5
        UNION ALL
        SELECT 12, CAST('02-02-2009' AS DATETIME), 0
        UNION ALL
        SELECT 77, CAST('02-14-2009' AS DATETIME), 7
        UNION ALL
        SELECT 39, CAST('02-20-2009' AS DATETIME), 34
        UNION ALL
        SELECT 33, CAST('03-02-2009' AS DATETIME), 6
    ) rt

SELECT
     t1.ID
    ,t1.SomeDate
    ,t1.SomeValue
    ,SUM(t2.SomeValue) AS RunningTotal
FROM
    #tmp t1
    JOIN #tmp t2
        ON t2.OrderID <= t1.OrderID
GROUP BY
     t1.OrderID
    ,t1.ID
    ,t1.SomeDate
    ,t1.SomeValue
ORDER BY
    t1.OrderID

DROP TABLE #tmp