2

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

Improve this question
0

3 Answers 3

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I m working on SQL Server 2012, so above query worked for me, it's so small and quite easy to understand I think I was unnecessarily adding the bottleneck of JOINS, anyways what was the mistake in the query I had used, Thanks
First, you should initialize @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.
@Deepesh, your query now would be: DECLARE @s INT = 0 SELECT @s = @s + Percent_of_qty FROM tblsumDemo ORDER BY billingid.
Just for completeness in case the OP is wonder why the 2012 code works, the default frame for 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)
ohh.. now I see it more distinctly, it saved my day.@Les H , @Felix pamittan.. bunch of thanks
2

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

Improve this answer

Comments

1

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;
Improve this answer

1 Comment

your query seems to take a while to execute, but anyways I got the solution from the previous answers

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.