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.

Answer 3

In Oracle and PostgreSQL 8.4 you can use window functions:

SELECT  SUM(value) OVER (ORDER BY id)
FROM    mytable

In MySQL, you can use a session variable for the same purpose:

SELECT  @sum := @sum + value
FROM    (
        SELECT  @sum := 0
        ) vars, mytable
ORDER BY
        id

In SQL Server, it's a rare example of a task for which a cursor is a preferred solution.

Answer 4

An example of calculating a running total for each record, but only if the OrderDate for the records are on the same date. Once the OrderDate is for a different day, then a new running total will be started and accumulated for the new day: (assume the table structure and data)

select O.OrderId,
convert(char(10),O.OrderDate,101) as 'Order Date',
O.OrderAmt, 
(select sum(OrderAmt) from Orders 
                      where OrderID <= O.OrderID and 
                           convert(char(10),OrderDate,101)
                         = convert(char(10),O.OrderDate,101))
                               'Running Total' 
from Orders O
order by OrderID

Here are the results returned from the query using sample Orders Table:

OrderId     Order Date OrderAmt   Running Total                            
----------- ---------- ---------- ---------------
1           10/11/2003 10.50      10.50
2           10/11/2003 11.50      22.00
3           10/11/2003 1.25       23.25
4           10/12/2003 100.57     100.57
5           10/12/2003 19.99      120.56
6           10/13/2003 47.14      47.14
7           10/13/2003 10.08      57.22
8           10/13/2003 7.50       64.72
9           10/13/2003 9.50       74.22

Note that the "Running Total" starts out with a value of 10.50, and then becomes 22.00, and finally becomes 23.25 for OrderID 3, since all these records have the same OrderDate (10/11/2003). But when OrderID 4 is displayed the running total is reset, and the running total starts over again. This is because OrderID 4 has a different date for its OrderDate, then OrderID 1, 2, and 3. Calculating this running total for each unique date is once again accomplished by using a correlated sub query, although an extra WHERE condition is required, which identified that the OrderDate's on different records need to be the same day. This WHERE condition is accomplished by using the CONVERT function to truncate the OrderDate into a MM/DD/YYYY format.

Answer 5

In SQL Server 2012 and up you can just use the Sum windowing function directly against the original table:

SELECT
   artranid,
   trandate,
   type,
   checkNumber,
   refNumber,
   custid,
   amount,
   taxAmount,
   Balance = Sum(amount) OVER (ORDER BY trandate ROWS UNBOUNDED PRECEDING),
   postedflag,
   modifieddate
FROM
   dbo.Sales
;

This will perform very well compared to all solutions and will not have the potential for errors as found in the "quirky update".

Note that you should use the ROWS version when possible; the RANGE version may perform less well.

Answer 6

You can just include a correlated subquery in the select clause. (This will perform poorly for very large result sets) but

   Select <other stuff>,
       (Select Sum(ColumnVal) From Table
        Where OrderColumn <= T.OrderColumn) As RunningTotal
   From Table T
   Order By OrderColumn

Answer 7

You can do a running count, here is an example, keep in mind that this is actually not that fast since it has to scan the table for every row, if your table is large this can be quite time consuming and costly

create table #Test  (id int, Value decimal(16,4))
insert #Test values(1,100)
insert #Test values(2,100)
insert #Test values(3,100)
insert #Test values(4,200)
insert #Test values(5,200)
insert #Test values(6,200)
insert #Test values(7,200)

select *,(select sum(Value) from  #Test t2 where t2.id <=t1.id) as SumValues
 from #test t1

id  Value       SumValues
1   100.0000    100.0000
2   100.0000    200.0000
3   100.0000    300.0000
4   200.0000    500.0000
5   200.0000    700.0000
6   200.0000    900.0000
7   200.0000    1100.0000

Answer 8

On SQLTeam there's also an article about calculating running totals. There is a comparison of 3 ways to do it, along with some performance measuring:

• using cursors
• using a subselect (as per SQLMenace's post)
• using a CROSS JOIN

Cursors outperform by far the other solutions, but if you must not use cursors, there's at least an alternative.

Answer 9

That that SELECT @nvcConcatonated bit is only returning a single concatenated value. (Although it's computing the intermediate values on a per-row basis, you're only able to retrieve the final value).

So, I think the answer is no. If you wanted a single final sum value you would of course just use SUM.

I'm not saying you can't do it, I'm just saying you can't do it using this 'trick'.

Answer 10

Note that using a variable to accomplish this such as in the following may fail in a multiprocessor system because separate rows could get calculated on different processors and may end up using the same starting value. My understanding is that a query hint could be used to force it to use a single thread, but I do not have that information handy.

UPDATE @SalesTbl SET @RunningTotal = RunningTotal = @RunningTotal + Sales FROM @SalesTbl

Using one of the other options (a cursor, a window function, or nested queries) is typically going to be your safest bet for reliable results.

Answer 11

select TransactionDate, amount, amount + (sum x.amount from transactions x where x.TransactionDate < Transactions) Runningtotal from Transactions

where x.TransactionDate < Transactions could be any condition that will represent all the previous records aside from the current one