How do I calculate a running total in SQL without using a cursor?

Question

I'm leaving out all the cursor setup and the SELECT from the temp table for brevity. Basically, this code computes a running balance for all transactions per transaction.

WHILE @@fetch_status = 0 BEGIN      set @balance = @balance+@amount      insert into @tblArTran values ( --from artran table                 @artranid, @trandate, @type,                  @checkNumber, @refNumber,@custid,                 @amount, @taxAmount, @balance, @postedflag, @modifieddate )       FETCH NEXT FROM artranCursor into              @artranid, @trandate, @type, @checkNumber, @refNumber,             @amount, @taxAmount,@postedFlag,@custid, @modifieddate  END 

Inspired by this code from an answer to another question,

SELECT @nvcConcatenated = @nvcConcatenated + C.CompanyName + ', ' FROM tblCompany C WHERE C.CompanyID IN (1,2,3) 

I was wondering if SQL had the ability to sum numbers in the same way it's concatonating strings, if you get my meaning. That is, to create a "running balance" per row, without using a cursor.

Is it possible?

Answer 1

You might want to take a look at the update to local variable solution here: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)  DECLARE @RunningTotal money  SET @RunningTotal = 0  INSERT INTO @SalesTbl  SELECT DayCount, Sales, null FROM Sales ORDER BY DayCount  UPDATE @SalesTbl SET @RunningTotal = RunningTotal = @RunningTotal + Sales FROM @SalesTbl  SELECT * FROM @SalesTbl 

Outperforms all other methods, but has some doubts about guaranteed row order. Seems to work fine when temp table is indexed though..

• Nested sub-query 9300 ms
• Self join 6100 ms
• Cursor 400 ms
• Update to local variable 140 ms

Answer 2

SQL can create running totals without using cursors, but it's one of the few cases where a cursor is actually more performant than a set-based solution (given the operators currently available in SQL Server). Alternatively, a CLR function can sometimes shine well. Itzik Ben-Gan did an excellent series in SQL Server Magazine on running aggregates. The series concluded last month, but you can get access to all of the articles if you have an online subscription.

Edit: here's his latest article in the series (SQL CLR). Given that you can access the whole series by purchasing an online monthly pass for one month - less than 6 bucks - it's worth your while if you're interested in looking at the problem from all angles. Itzik is a Microsoft MVP and a very bright TSQL coder.