Deepesh Deepesh - 7 months ago 18
SQL Question

Calculating cumulative sum in ms-sql

I have a table tblsumDemo with the following structure

billingid qty Percent_of_qty cumulative
1 10 5 5
2 5 8 13(5+8)
3 12 6 19(13+6)
4 1 10 29(19+10)
5 2 11 40(11+10)


this is what I have tried

declare @s int
SELECT billingid, qty, Percent_of_qty,
@s = @s + Percent_of_qty AS cumulative
FROM tblsumDemo
CROSS JOIN (SELECT @s = 0) AS var
ORDER BY billingid


but I'm not able to get the desired output,any help would be much appreciated , Thanks

Answer

You can use CROSS APPLY:

SELECT 
    t1.*,
    x.cumulative
FROM tblSumDemo t1
CROSS APPLY(
    SELECT 
        cumulative = SUM(t2.Percent_of_Qty)
    FROM tblSumDemo t2
    WHERE t2.billingid <= t1.billingid
)x

For SQL Server 2012+, you can use SUM OVER():

SELECT *,
    cummulative = SUM(Percent_of_Qty) OVER(ORDER BY billingId)
FROM tblSumDemo