SQLTeam.com | Weblogs | Forums

Please Help Me to write the query


#1

Hi Team,
I have a column with name Qty from table Bills i want a column that show the running sum of Qty column like this :

Qty Run_Sum
1 0
2 1
3 3
4 6
5 10
Please help me to solve this


#2
SELECT
	Qty,
	SUM(Qty) OVER (ORDER BY Qty) as Run_Sum
FROM
	YourTable;

#3

will you have duplicate Qty ?


#4

Yes.We cannot restrict.


#6

Yes khtan.We cannot restrict.


#7
; with cte as
(
    select    rn = row_number() over (order by Qty), Qty
    from    yourtable
)
select    Qty, Run_Sum = sum(Qty) over (order by rn) - Qty
from    cte

#8

Still we are getting the error "Incorrect syntax near 'order'".


#9

What version of SQL Server are you using? The query works only for SQL 2012 or later.


#10

this works for before SQL 2012

; with cte as
(
    select    rn = row_number() over (order by Qty), Qty
    from    your_table
)
select    Qty, Run_Sum = Run_Sum - Qty
from    cte d
    cross apply
    (
        select    Run_Sum = sum(Qty)
        from    cte x
        where    x.rn    <= d.rn
    ) rs