Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

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


Comments

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

Loading comments...