10 replies

Last post Jun 05, 2013 06:16 AM by rasadulalam

Member

38 Points

124 Posts

Cumulative Sum in SQL

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...

Mark this post as answer if it helps you!!!

Regards
Naveen Sanagasetti...

Participant

1040 Points

418 Posts

Re: Cumulative Sum in SQL

Feb 26, 2013 04:44 AM|raghavendra ms|LINK

Contributor

7236 Points

1572 Posts

Contributor

3340 Points

986 Posts

Re: Cumulative Sum in SQL

Try as below,

create table #table (Id int, [Date] date, Qty int);

insert into #table values (1,'2011-02-23',100);
insert into #table values (1,'2011-03-18',300);
insert into #table values (1,'2013-04-23',100);
insert into #table values (2,'2010-01-20',50);
insert into #table values (2,'2013-03-23', 100);

temp table for query,

create table #table1(Id1 int identity(1,1), Id int, [Date] date, Qty int, Cummulative int);

insert into #table1 select *, 0 Cummulative from #table;

update #table1 set Cummulative=(select SUM(#table.qty) from #table where #table.Id=#table1.Id and #table.[Date]<=#table1.[Date]);

select * from #table1;

Contributor

3340 Points

986 Posts

Re: Cumulative Sum in SQL

You can also get this result by using cte as below,

;WITH cte AS (
SELECT Row_number() OVER (ORDER BY id, [Date]) AS rownum,*
FROM #table
)

SELECT a.Id, a.Date, a.Qty, SUM(b.Qty) AS Cumulative
FROM cte a
LEFT JOIN cte b ON a.id = b.id AND b.rownum <= a.rownum
GROUP BY a.id, a.rownum, a.[date], a.Qty
ORDER BY a.id, a.[Date]

Contributor

5430 Points

4779 Posts

Re: Cumulative Sum in SQL

Try

with cte as
(select  ID,Date,Qty,ROW_NUMBER () Over(partition by id Order by date) rn
from tab1)
select  ID,Date,Qty,(select sum(qty) from cte b where b.id=a.id and b.rn<=a.rn) Cummulative
from cte a
order by 1,2;

Many Thanks & Best Regards,
HuaMin Chen

Member

38 Points

124 Posts

Re: Cumulative Sum in SQL

I have a small doubt with my query can you please resolve this

;WITH cte AS (
SELECT Row_number() OVER (ORDER BY Material_Id, [Planned_date]) AS rownum,*
FROM #TempTable
)
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 Cumulative
FROM cte a
LEFT JOIN cte b ON a.Material_Id = b.Material_Id AND b.rownum <= a.rownum
GROUP BY a.Proj_code,a.Material_Id, a.DED_Description,a.DED_Short_Desc,a.rownum, a.[Planned_date], a.Planned_Material_qty
ORDER BY a.Material_Id, a.[Planned_date]

Using this my result is look like this

Material_Id	DED_Description	Planned_date	            Material_qty	Cumulative
2	                 RCC	2011-02-02 00:00:00.000	      200	200
2	                 RCC	2011-03-02 00:00:00.000	     100	300
2	                 RCC	2012-11-02 00:00:00.000	      300	600
2	                 RCC	2012-12-02 00:00:00.000	      200	800
2	                 RCC	2013-01-02 00:00:00.000	       150	950
2	                 RCC	2013-02-02 00:00:00.000	       100	1050
2	                 RCC	2013-03-02 00:00:00.000	         50	1100
4	                 Piping	2012-11-02 00:00:00.000	       300	300
4	                 Piping	2012-12-02 00:00:00.000	       200	500
4	                 Piping	2013-01-02 00:00:00.000	       100	600
4	                 Piping	2013-02-02 00:00:00.000	         80	680
4	                 Piping	2013-03-02 00:00:00.000	         50	730

now, i want to display the current month qty, previous month qty & cummulative upto prev month...

How to achieve this ...

Mark this post as answer if it helps you!!!

Regards
Naveen Sanagasetti...

Contributor

5430 Points

4779 Posts

Re: Cumulative Sum in SQL

Try

;WITH cte AS (
SELECT Row_number() OVER (ORDER BY Material_Id, [Planned_date]) AS rownum,*
FROM #TempTable
)
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 Cumulative,(select sum(Planned_Material_qty) from cte c where a.Material_Id = c.Material_Id and substring(convert(varchar,a.Planned_date,112),1,6)=substring(convert(varchar,c.Planned_date,112),1,6)) as current_month_total,(select sum(Planned_Material_qty) from cte c where a.Material_Id = c.Material_Id and substring(convert(varchar,dateadd(mm,-1,a.Planned_date),112),1,6)=substring(convert(varchar,c.Planned_date,112),1,6)) as previous_month_total
FROM cte a
LEFT JOIN cte b ON a.Material_Id = b.Material_Id AND b.rownum <= a.rownum
GROUP BY a.Proj_code,a.Material_Id, a.DED_Description,a.DED_Short_Desc,a.rownum, a.[Planned_date], a.Planned_Material_qty
ORDER BY a.Material_Id, a.[Planned_date]

Many Thanks & Best Regards,
HuaMin Chen
• limno

All-Star

119378 Points

9004 Posts

Moderator

Re: Cumulative Sum in SQL

--SQL Server 2012

create table test1 (id int, [Date] date, Qty int);

insert into test1 values (1,'2011-02-23',100);
insert into test1 values (1,'2011-03-18',300);
insert into test1 values (1,'2013-04-23',100);
insert into test1 values (2,'2010-01-20',50);
insert into test1 values (2,'2013-03-23', 100);

SELECT id,date,qty

,SUM(Qty) OVER(Partition BY id  ORDER BY date) as Cummulative

FROM   test1
ORDER  BY id, date

drop table test1

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Contributor

5274 Points

1083 Posts

Re: Cumulative Sum in SQL

Sandeep Mittal | My Blog - IT Developer Zone

Member

44 Points

56 Posts

Re: Cumulative Sum in SQL

Some time we need to do cumulative sum in a table. In SQL we can easily do it. The following code will help us.

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 with code:

http://cybarlab.com/cumulative-sum-in-sql

Thanks and best regard.

Rashed