Comments (0)

By Sean, 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