Skip to content Skip to sidebar Skip to footer

Sql - Running Total When Data Already Grouped

I am trying to do a running total for some data, and have seen the easy way to do it. However, I have already grouped some data and this is throwing off my code. I currently have d

Solution 1:

The ANSI standard way of doing a cumulative sum is:

select t.*, sum(totalpmtamt) over (orderby mdate) as runningsum
from #testdata t
orderby t.mdate;

Not all databases support this functionality.

If your database doesn't support that functionality, I would go for a correlated subquery:

select t.*,
       (selectsum(t2.totalpmtamt)
        from #testdata t2
        where t2.mdate <= t.mdate
       ) as runningsum
from#testdata
order by t.mdate;

Solution 2:

Use the below query for the desired result (for SQL Server).

with cte_1
     as
     (SELECT*,ROW_NUMBER() OVER(orderby mdate  ) RNO
     FROM #testdata)
     SELECT mdate,pmttype,totalpmtamt,(selectsum(c2.totalpmtamt)
        from cte_1 c2
        where c2.RNO <= c1.RNO
       ) as incrtotal
     FROM cte_1 c1

Output :

enter image description here

Solution 3:

Sounds like SQL Server.

DECLARE@testdataTABLE
    (
      mdate DATE ,
      pmttype VARCHAR(64) ,
      totalpmtamt INT
    );

INSERTINTO@testdata
        ( mdate, pmttype, totalpmtamt )
VALUES  ( GETDATE() -7, 'DD', 10 ),
        ( GETDATE() -7, 'SO', 12 ),
        ( GETDATE() -6, 'DD', 3 ),
        ( GETDATE() -5, 'DD', 13 ),
        ( GETDATE() -5, 'SO', 23 ),
        ( GETDATE() -5, 'PO', 8 );

SELECT*,
        SUM(totalpmtamt) OVER ( ORDERBY mdate ROWS UNBOUNDED PRECEDING )
        AS RunningTotal
FROM@testdata t;

Post a Comment for "Sql - Running Total When Data Already Grouped"