Blog Post

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

Rate

Share

Share

Rate