Saturday 19 October 2013

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


9 comments:

  1. See
    Calculating Values within a Rolling Window in Transact SQL https://www.simple-talk.com/sql/t-sql-programming/calculating-values-within-a-rolling-window-in-transact-sql/ for some alternative ways of doing it that are faster.

    ReplyDelete
  2. Careful now. This is known as a "Triangular Join" and it's one of the classic methods to totally paralyze your server and drive I/O right into the stops. Please see the following article that explains the doom that can be caused by Triangular Joins.
    http://www.sqlservercentral.com/articles/T-SQL/61539/

    ReplyDelete
  3. Careful now. This is known as a "Triangular Join" and it's one of the classic methods to totally paralyze your server and drive I/O right into the stops. Please see the following article that explains the doom that can be caused by Triangular Joins.
    http://www.sqlservercentral.com/articles/T-SQL/61539/

    ReplyDelete
  4. I used another way to do this and got the same result:

    select ID,val,sum(val)
    over ( order by ID
    rows unbounded preceding) as cumSum
    from testsum

    ReplyDelete
  5. This is the way I did it which produced same results:

    select ID,val,sum(val)
    over ( order by ID
    rows unbounded preceding) as cumSum
    from testsum

    ReplyDelete
    Replies
    1. rows unbounded preceding is a new feature of SQL SERVER 2012.It will not work in other versions of SQL Server.

      Delete
  6. Any comments on the following lines of code for calculating cumulative sum?

    SELECT A2.*, B2.CumSum
    FROM testsum A2
    CROSS APPLY (
    SELECT SUM(A1.val) AS CumSum
    FROM testsum A1
    WHERE A1.ID <= A2.ID
    ) B2

    ReplyDelete

Please leave a comment for this post