Calculate Running Total In SQL Server

Jump To Right Section Show

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:


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

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 Name, Gender, Salary,
SUM(Salary) OVER (PARTITION BY Gender ORDER BY ID) AS RunningTotal
FROM Employees

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.


;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);

;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

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

Calculate a running total in SQL Server 2012

Running Total in SQL Server 2012

SQL Server 2005 and 2008

Leave a Comment X

Calculate a running total in SQL Server 2012

Running Total in SQL Server 2012

SQL Server 2005 and 2008

You may also like

Leave a Comment X