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