## Running Totals

By , February 7, 2011 19:02

This morning I read a blog post where a solution for calculating running totals was presented. I wanted to post a code correction as a comment to the post itself, but after three attempts (three site errors) to submit my comment, I gave up and decided to do a quick post of my own.

I don’t want to drag anybody through the mud on this, so I wont post a link to the blog, but this is how the code was presented (I have edited it so that it can’t be used to search for the original post).

 ```DECLARE @RunningTotal TABLE (Amount INT) INSERT INTO @RunningTotal VALUES (1); INSERT INTO @RunningTotal VALUES (2); INSERT INTO @RunningTotal VALUES (3); INSERT INTO @RunningTotal VALUES (4); INSERT INTO @RunningTotal VALUES (5); SELECT a.Amount ,( SELECT Sum(b.Amount) FROM @RunningTotal b WHERE b.Amount <= a.Amount ) rs FROM @RunningTotal a```

I found the idea fairly nifty, but what I wanted to challenge was the fact that this script is entirely dependant on the order of the values stored in Amount and that if a particular amount were to be entered more than once, the results would be incorrect.

Try the above with the following values.

 ```INSERT INTO @RunningTotal VALUES (1); INSERT INTO @RunningTotal VALUES (2); INSERT INTO @RunningTotal VALUES (3); INSERT INTO @RunningTotal VALUES (4); INSERT INTO @RunningTotal VALUES (5); INSERT INTO @RunningTotal VALUES (2);```

Even if pre-sorted, the results would be partially incorrect.

It did however make me think about how I would get the required results. My first idea was to use ROW_NUMBER() and I ended up having to use a CTE too. But it works no matter what order the rows are in!

 ```;WITH RunningTotal_CTE( RowNo, Amount) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as RowNo, Amount FROM @RunningTotal ) SELECT a.Amount, ( SELECT SUM (b.Amount) FROM RunningTotal_CTE b WHERE b.RowNo <= a.RowNo ) RunningTotal FROM RunningTotal_CTE a```