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:

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.