Calculate a running total in SQL Server 2012

Running total 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.account, a.deposit, SUM(a.deposit) OVER (ORDER BY AS 'total'
FROM #TestData a

Results showing the running total on test data

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

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

Results with partition showing running total within named groups

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


SELECT, a.account, a.deposit, + a.deposit
FROM cte JOIN #TestData a ON + 1 =

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 <= AS 'total'
FROM #TestData a