Recursive SQL- How can I get this table with a running total?

Question

ID      debit   credit  sum_debit
---------------------------------
1       150     0       150
2       100     0       250
3       0       50      200
4       0       100     100
5       50      0       150

I have this table, my problem is how to get sum_debit column which is the total of the previous row sum_debit with debit minus credit (sum_debit = sum_debit + debit - credit). each new row I enter debit but credit data is zero, or by entering the value of credit and debit is zero. How do I get sum_debit?

Are you really only trying to track one account? Are `ID` values (somehow) guaranteed to be contiguous, and are those the way that you define "previous"? — Damien_The_Unbeliever, Jun 11 '13 at 18:04
ID is the AutoNumber column, did not limit the number of rows can be more rows. I use Microsoft SQL Server. Previous means to use the previous value sum_debit if the current ID 4, ID is the previous 3. — Asmir Jusovic, Jun 11 '13 at 18:15

score 4 · Answer 1 · edited Apr 13 '17 at 12:42

4

In SQL-Server 2012, you can use the newly added ROWS or RANGE clause:

SELECT 
    ID, debit, credit,
    sum_debit = 
        SUM(debit - credit) 
        OVER (ORDER BY ID
              ROWS BETWEEN UNBOUNDED PRECEDING
                       AND CURRENT ROW
             )
FROM 
    CreditData
ORDER BY
    ID ;

Tested in SQL-Fiddle

We could just use OVER(ORDER BY ID) there and the result would be the same. But then the default would be used, which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and there are efficiency differences (ROWS should be preferred with running totals.)

There is a great article by @Aaron Bertrand, that has a thorough test of various methods to calculate a running total: Best approaches for running totals – updated for SQL Server 2012

For previous versions of SQL-Server, you'll have to use some other method, like a self-join, a recursive CTE or a cursor. Here is a cursor solution, blindly copied from Aaron's blog, with tables and columns adjusted to your problem:

DECLARE @cd TABLE
(   [ID] int PRIMARY KEY, 
    [debit] int, 
    [credit] int,
    [sum_debit] int
);

DECLARE
    @ID           INT,
    @debit        INT,
    @credit       INT,
    @RunningTotal INT = 0 ;

DECLARE c CURSOR
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
    SELECT ID, debit, credit
      FROM CreditData
      ORDER BY ID ;

OPEN c ;

FETCH NEXT FROM c INTO @ID, @debit, @credit ;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal = @RunningTotal + (@debit - @credit) ;

    INSERT @cd (ID, debit, credit, sum_debit )
        SELECT @ID, @debit, @credit, @RunningTotal ;

    FETCH NEXT FROM c INTO @ID, @debit, @credit ;
END

CLOSE c;
DEALLOCATE c;

SELECT ID, debit, credit, sum_debit
    FROM @cd
    ORDER BY ID ;

Tested in SQL-Fiddle-cursor

edited Apr 13 '17 at 12:42

Community

1
1

answered Jun 11 '13 at 18:21

ypercubeᵀᴹ

113,259
19
174
235

you don't need to specify `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` - that is the default. The running sum will automaticlly be calculated when you specify the `order by` in the window definition. – Jun 11 '13 at 19:11
@a_horse_with_no_name No, the default is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` and while the result is the same, there are efficiency differences, see the article of @Aaron: **[Best approaches for running totals – updated for SQL Server 2012](http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals)** – ypercubeᵀᴹ Jun 11 '13 at 19:13
I receive error in my SQL SERVER 2005, like: Incorrect syntax near 'ROWS'. if there is some other way or command that accepts SQL Server 2005? – Asmir Jusovic Jun 11 '13 at 19:43
This answer will work only in 2012, not in previous versions. – ypercubeᵀᴹ Jun 11 '13 at 19:45
Interesting. Seems SQL Server behaves completely different than Postgres and Oracle here. There is absolutel no difference in execution plans in those DBMS, and no difference in the amount of data read or processed. – Jun 11 '13 at 19:46
@a_horse_with_no_name Based on Aaron's post, this is because RANGE uses an on-disk spool, while ROWS uses an in-memory spool. – ypercubeᵀᴹ Jun 11 '13 at 19:48
Oracle and Postgres use an in-memory sort for both versions. Nothing spooled to disk there (and used Aaron's test setup, but with 100.000 rows) – Jun 11 '13 at 19:49

score 2 · Accepted Answer · edited May 23 '17 at 12:09

2

Assuming "have" is your data table, this should be an ANSI SQL solution:

select h.*, sum(i.debit) as debsum, sum(i.credit) as credsum, sum(i.debit) - sum(i.credit) as rolling_sum
from have h inner join have i
on h.id >= i.id
group by h.id, h.debit, h.credit
order by h.id

In general, the solution is to join the row to all rows preceding the row, and extract the sum of those rows, then group by everything to get back to one row per what you expect. Like this question for example.

edited May 23 '17 at 12:09

Community

1
1

answered Jun 11 '13 at 18:21

Joe

62,789
6
49
67

1

This will be a really terribly performing solution. – JNK Jun 11 '13 at 18:41
Probably, but I don't know that you can do better in ANSI, can you? This was before he clarified it was SQL Server 2012 :) – Joe Jun 11 '13 at 18:44
Actually the OP hasn't clarified the version. He may well be using 2000. – ypercubeᵀᴹ Jun 11 '13 at 18:46
`*shudders*`... I hope not. Either way I think it's worth having the ANSI solution (and if there's a better one, please post!) for searchers even if the OP can use the far superior SQL Server specific one. – Joe Jun 11 '13 at 18:49
@Joe the best solution is probably going to be a cursor, believe it or not. – JNK Jun 11 '13 at 18:49
@JNK Hmm, I'd love to see that. I'm actually a SAS programmer, so don't have cursors available (since SAS native code is far superior to what any cursor implementation would be) and thus don't know them very well. – Joe Jun 11 '13 at 18:52
Does SAS have this `ge` operator? Because that's not valid in SQL. And my answer is not SQL-Server specific. It's ANSI SQL all right (but not ANSI 1992). – ypercubeᵀᴹ Jun 11 '13 at 18:54
Good point, I use those so often I forget they're not standard everywhere :) – Joe Jun 11 '13 at 19:12
select h.*, sum(i.debit) as debsum, sum(i.credit) as credsum, sum(i.debit) - sum(i.credit) as rolling_sum from have h inner join have i on h.id >= i.id group by h.id, h.debit, h.credit order by h.id
This has resolved my problem – Asmir Jusovic Jun 11 '13 at 19:53
Glad it helped; I do agree with the others though, it's quite inefficient. If you need to do this for a lot of rows, use the cursor solution. – Joe Jun 11 '13 at 21:04

Recursive SQL- How can I get this table with a running total?

2 Answers2

Linked