Cumulative sum in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

How to achieve this output, I have a columns like ID,Date,Qty but i want cumulative qty.

ID           Date       Qty       Cummulative
1          2011-02-23   100        100
1          2011-03-18   300        400
1          2013-04-23   100        500
2          2010-01-20    50         50
2          2013-03-23   100        150

How to achieve this , i'm searching in google for this but that's not satisfied my requirement..

can anybody help me out of this...

Posted 25-Feb-13 18:38pm

Naveen.Sanagasetti

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Arul R Ece · Answer 1 · 2013-02-25T18:43:00

Solution 1

Hi Naveen ,

Please look into the example.

It may be help you .

SQL

Create table #tt1 (sno int identity,Amt int)

insert into #tt1 values (100)
insert into #tt1 values (200)
insert into #tt1 values (300)
insert into #tt1 values (400)

select * from #tt1

select a.sno,a.Amt,Sum(b.Amt) as Cum_Amt from #tt1 a cross join #tt1 b
where a.sno>=b.sno group by a.sno,a.Amt
order by a.sno,a.Amt

Posted 25-Feb-13 18:43pm

Arul R Ece

Comments

Naveen.Sanagasetti 26-Feb-13 0:47am

I tried like this but my output is mismatch to my requirement

select a.Proj_code,a.Material_Id,a.DED_Description,a.DED_Short_Desc,a.Planned_date,

a.Planned_Material_qty, sum(b.Planned_Material_qty) As Total

from #TempTable a cross join #TempTable b where (b.Material_Id=a.Material_Id)

group by a.Proj_code,a.Material_Id,a.DED_Description,a.DED_Short_Desc,a.Planned_date,

a.Planned_Material_qty order by Material_Id

Singh_Vinay · Answer 2 · 2013-02-25T19:35:00

Try this->

SQL

declare  @t table
(
    id int,
    SomeNumt int
)

insert into @t
select 1,10
union
select 2,12
union
select 3,3
union
select 4,15
union
select 5,23


select t1.id, t1.SomeNumt, SUM(t2.SomeNumt) as sum
from @t t1
inner join @t t2 on t1.id >= t2.id
group by t1.id, t1.SomeNumt
order by t1.id

World Traveler · Answer 3 · 2013-06-04T20:18:00

SQL

SELECT      T1.SL,
            T1.GroupName,
            T1.Amount,
            SUM(T2.Amount) as CumulativeSum
FROM  @Temp T1 INNER JOIN
            @Temp T2 on T1.SL >= T2.SL
GROUP BY T1.SL,T1.GroupName, T1.Amount
ORDER BY T1.SL

For more in details:

http://cybarlab.blogspot.com/2013/06/cumulative-sum-in-sql.html[^]

gvprabu · Answer 4 · 2013-06-04T22:03:00

Solution 4

Hi,

Try like this....

SQL

SELECT ID,Date,Qty (SELECT SUM(Qty) FROM tablname WHERE ROWNUMBER() OVER(ORDER BY ID) <= T.RowNo) 'CumulativeSum'
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID) 'RowNo', ID,Date,Qty FROM table_Name)T

Regards,
GVPrabu

Posted 4-Jun-13 22:03pm

gvprabu

Cumulative sum in SQL

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0