In this tutorial, we discuss how to easily calculate a running total in SQL Server 2012 or above. Our goal is to calculate the running total that resets whenever the Transaction Date changes. We’ll total the transaction amount. For each subsequent invoice within the transaction date, the Running Total should equal the prior InvoiceID’s running total plus the current transaction amount.
Running Total in SQL Server 2012
In the previous versions of SQL Server, calculating a running total (like 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:
|
1 2 3 4 |
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:
|
1 2 3 4 5 |
SELECT Name, Gender, Salary, SUM(Salary) OVER (PARTITION BY Gender ORDER BY ID) AS RunningTotal FROM Employees |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
;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:
|
1 2 3 4 |
SELECT a.*, (SELECT SUM(b.deposit) FROM #TestData b WHERE b.id <= a.id) AS 'total' FROM #TestData a |
The article was published on August 13, 2018 @ 10:13 AM
Leave a Comment