CODEWITHSUNDEEP
sqlsql-server
Asmir Jusovic
Asmir Jusovic

Reputation: 13

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

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?

Upvotes: 1

Views: 1730

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 4

Joe
Joe

Reputation: 63424

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.

Upvotes: 2

Related Questions