Calculating running totals

A lot has been written about it on the internet already, but I thought it was worth posting an article about calculating running totals in Microsoft SQL Server 2005 / 2008. There are several ways to do this. This article covers the fastest one.

SQL Server 2005 + has no good way of its own to calculate running totals. It cannot add the previous row to the current row by itself. You have to find a way to add the previous row to the current row.

The most common method, and the easiest to understand, is the self-join. With this method you “Join” the dataset to itself, so you can add up the result of the previous records every time. But this is not the most effective method!

The fastest method uses a table variable or temporary table (@ or #). Let me explain this with an example. We use AdventureWorksDW for it. Say we want to calculate the running totals of the internet sales per customer over time.

First we create a table variable (@) and fill it with all internet sales per customer per week. We sort the data when we insert it. This is absolutely necessary, because after that we update the records one by one with running totals.

1
USE AdventureWorksDW
2
GO
3
DECLARE @RunningTotalAantal INT
4
DECLARE @RunningTotalBedrag MONEY
5
DECLARE @KlantNaam AS VARCHAR(128)
6

7
DECLARE @Sales TABLE (WeekNaam VARCHAR(128), KlantNaam VARCHAR(128), Aantal INT, Bedrag MONEY, AantalCumulatief INT, BedragCumulatief MONEY)
8

9
INSERT INTO @Sales
10
SELECTDT.CalendarYear * 100 + DT.WeekNumberOfYear AS WeekNaam
11
, DC.FirstName + ' ' + DC.LastName+ ' (' + CAST(DC.CustomerKey AS VARCHAR(16)) + ')' AS KlantNaam
12
, SUM(FIS.OrderQuantity) AS Aantal
13
, SUM(FIS.SalesAmount) AS Bedrag
14
, 0
15
, 0
16
FROM FactInternetSales AS FIS
17
INNER JOIN DimProduct AS DP ON FIS.ProductKey = DP.ProductKey
18
INNER JOIN DimCustomer AS DC ON FIS.CustomerKey = DC.CustomerKey
19
INNER JOIN DimTime AS DT ON FIS.OrderDateKey = DT.TimeKey
20
GROUP BY DC.FirstName,DC.LastName, DC.CustomerKey
21
, DT.CalendarYear,DT.WeekNumberOfYear
22
-- Sorting is very important here!
23
ORDER BY DC.FirstName,DC.LastName
24
, DT.CalendarYear,DT.WeekNumberOfYear

As you can see in the example above, we sum the number of items sold and the order amount per customer per week. We set the values AantalCumulatief and BedragCumulatief to 0.

The temporary table now holds all internet sales per customer. Next we calculate the last two columns. This is what it is all about. It is a neat little trick and very effective!

1
UPDATE @Sales
2
SET @RunningTotalAantal = AantalCumulatief =
3
(CASE WHEN KlantNaam = @KlantNaam THEN @RunningTotalAantal + Aantal
4
ELSE AantalEND)
5
,@RunningTotalBedrag = BedragCumulatief =
6
(CASE WHEN KlantNaam = @KlantNaam THEN @RunningTotalBedrag + Bedrag
7
ELSE BedragEND)
8
,@KlantNaam = KlantNaam
9
FROM @Sales AS S
10
SELECT *
11
FROM @Sales

What you do here is fill the variable RunningTotalAantal with AantalCumulatief. For the first record it is empty. This “Aantal Cumulatief” is then filled with the field Aantal. When we take the first record, klantnaam <> @Klantnaam, because this variable is not filled yet. RunningTotalAantal and AantalCumulatief are now filled with the value of Aantal. The same happens with the column BedragCumulatief. Now comes the trick: @KlantNaam = Klantnaam. The variable is filled with the current customer name. We do this to find out if we are at a new customer, because then the counting has to start again. You have to look at the SQL carefully to understand it.

So you can use this method to calculate statistics columns in your data warehouse. I needed it to calculate the number of hours worked at customers per employee. On AdventureWorksDW you will not gain much performance compared to self joins or cursors, but if you have large tables with a few million rows, this is really fast. I tested this on a table with 5 million records. Calculating the running totals took 1 minute. Updating the FactTable also took 1 minute.

Questions? Leave a message :-)

Running_Totals_AdventureWorks