SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cumulative SUM in SQL Server


Sometimes we need to find sum of first and next row in cumulative way.

Create table and insert data:

CREATE TABLE [dbo].[testsum](
      [name] [varchar](10) NULL,
      [val] [int] NULL,
      [ID] [int] NULL
) ON [PRIMARY]

insert into [testsum] (id,name,val)
values(1,'A',10),
(2,'B',20),
(3,'C',30)

Required Output:
ID    name  val   cumSum
1     A     10    10
2     B     20    30
3     C     30    60

To find cumulative sum first you need to self join on condition >=

select t1.*,t2.* from testsum t1 inner join testsum t2 on t1.ID>=t2.ID

output after join.
t1
t2
ID
name
val
ID
name
val
1
A
10
1
A
10
2
B
20
1
A
10
3
C
30
1
A
10
2
B
20
2
B
20
3
C
30
2
B
20
3
C
30
3
C
30

Group by ID and SUM.

select t1.id, t1.val, SUM(t2.val) as cumSum
from testsum t1
inner join testsum t2 on t1.id >= t2.id
group by t1.id, t1.val
order by t1.id

t1
t2
ID
name
val
ID
name
val
1
A
10
1
A
10
2
B
20
1
A
10
3
C
30
1
A
10
2
B
20
2
B
20
3
C
30
2
B
20
3
C
30
3
C
30

We reach to output:

id
val
cumSum
1
10
10
2
20
30
3
30
60


Querying Microsoft SQL Server

I am a technology enthusiast and software developer by profession. I am developing .Net/database based enterprise applications from past 3 years.

My skills includes C# ,ASP.NET,SQL Server 2008 and MVC . My areas of interests are database development and application software development using Microsoft Technologies.

Comments

Leave a comment on the original post [www.queryingsql.com, opens in a new window]

Loading comments...