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 @nvcConcatonated = @nvcConcatonated + 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

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 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

view this

http://stackoverflow.com/questions/1124317/query-for-total-should-keep-on-adding-with-each-row-of-cost-column/1124682#1124682

Answer 4

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 5

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 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

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 8

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

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 9

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.