Calculate a running total in SQL Server 2012

In the previous versions of SQL Server, calculating a running total for say, a bank account ledger, has been a frustratingly complex task. Fortunately SQL Server 2012 makes this a breeze with new support for windowed aggregate functions.

In this test example we’ll be creating a running total for an imaginary ledger using the OVER clause with SUM (you can get the entire test script here):

SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (ORDER BY a.id) AS 'total'
    FROM #TestData a
    ORDER BY a.id;

You can even do a running total for each account separately using the PARTITION clause:

SELECT a.id, a.account, a.deposit, SUM(a.deposit) OVER (PARTITION BY a.account ORDER BY a.id) AS 'total'
    FROM #TestData a
    ORDER BY a.id;

SQL Server 2005 and 2008

For those still stuck on older versions of SQL Server, the solution is a little less straightforward. There are a lot of techniques, but I have found the best performance using a recursive CTE (Common Table Expression), a feature added in SQL Server 2005.

;WITH cte AS (
    SELECT id, account, deposit, deposit AS 'total'
        FROM #TestData
        WHERE id = 1

    UNION ALL

    SELECT a.id, a.account, a.deposit, cte.total + a.deposit
        FROM cte JOIN #TestData a ON cte.id + 1 = a.id
)
SELECT * FROM cte
OPTION (MAXRECURSION 32767);

There is one caveat though, since CTEs can do at most 32767 recursions. So if you have more records than that, you must fall back on the old sub-select:

SELECT a.*, (SELECT SUM(b.deposit) FROM #TestData b WHERE b.id <= a.id) AS 'total'
    FROM #TestData a