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:

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

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.

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:

The article was published on August 13, 2018 @ 10:13 AM

Leave a Comment