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
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 
@s to 0. Then remove other columns in the SELECT as it is not allowed to have variable assignment and select columns at the same time. Then, remove the CROSS JOIN.DECLARE @s INT = 0 SELECT @s = @s + Percent_of_qty FROM tblsumDemo ORDER BY billingid.
OVER with an ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW i.e. in longhand: SUM(Percent_Of_Qty) OVER (ORDER BY billingId RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)You can use subquery which works in all versions:
select billingid,qty,percentofqty, (select sum(qty) from tblsumdemo t2 where t1.id<=t2.id) as csum from tblsumdemo t1 you can use windows functions as well from sql 2012:
select *, sum(qty) over (order by qty rows between unbounded PRECEDING and current row) as csum from tblsumdemo Here i am saying get me sum of all rows starting from first row for every row(unbounded preceeding and current row).you can ignore unbounded preceeding and current row which is default
Use ROW_NUMBER just to order the billingID in ascending order, then Use join.
Query
;with cte as( select rn = row_number() over( order by billingid ), * from tblSumDemo ) select t1.billingid, t1.qty, t1.Percent_of_qty, sum(t2.Percent_of_qty) as cummulative from cte t1 join cte t2 on t1.rn >= t2.rn group by t1.billingid, t1.qty, t1.Percent_of_qty; 