Mar 11, 2011

Cumulative sum in Sql Server

There are numerous ways to compute cumulative sum in SQL Server.

In this post, we will see how to compute cumulative sum in SQL Server using Row_Number and self join.

NOTE: This would work on SQL Server 2005 onwards

Let's take an example where we will calculate student wise cumulative sum of marks.

First create a Marks table and populate some data in it

create table tblMarks (
    studid  varchar(20)
    , subcode varchar(20)
    , marks   int
)

insert into tblMarks
select 'Stud1', 'English', 60 union all
select 'Stud1', 'History', 70 union all
select 'Stud1', 'Maths', 80 union all
select 'Stud1', 'Science', 75 union all
select 'Stud2', 'English', 55 union all
select 'Stud2', 'History', 60 union all
select 'Stud2', 'Maths', 57 union all
select 'Stud2', 'Science', 65
Now let write query to calculate cumulative sum for all subjects student wise

;with cte as (
    select row_number() over (order by studid, subcode) as rownum,*
    from tblMarks
)
select a.studid, a.subcode, a.marks, SUM(b.marks) AS [Cumulative Sum]
from cte a
left join cte b on a.studid = b.studid AND b.rownum <= a.rownum
group by a.studid, a.rownum, a.subcode, a.marks
order by a.studid, a.subcode
OUTPUT
Cumulative Sum Output

Refer related post to calculate cumulative sum using recursive CTE,
Running total in SQL Server

Refer related post for SQL Server 2012,
Cumulative Sum in SQL Server 2012

    Choose :
  • OR
  • To comment
6 comments:
Write Comments
  1. Hey first of all.. thanks for this post. I've done the same Cumulative Sum in mysql using joins but somehow couldn't manage to re-do it on ORACLE.

    Your post worked for me... except for two things that I think you should check...

    1) I HAD TO RENAME THE ROWNUM for "ROWNUMBE" because 'rownum' is a function in Oracle and can't be used as a nickname

    2) IT SHOULD BE LESS OR EQUAL:

    b.rownumbe <= a.rownumbe

    If I don't this.. it just stay the same and don't do the cumulative sum


    Hope it helps!
    Thx!

    ReplyDelete
  2. This is good and works well except i believe you didnt need the join on a.studid = b.studid. Your solution was the easiest to comprehend from the number of possibilities given on the net and various blogs.
    Thanks a lot
    Wishing you all the best!

    simba01@gmail.com

    ReplyDelete
  3. Dear Rahul

    join on a.studid = b.studid is there to get student wise cumulative sum. without it, the query would do the cumulative sum of all the students

    ReplyDelete
    Replies
    1. I agree. I didnt notice that you had a different table than mine.

      Delete
  4. I would like to thank you for the efforts you have made in writing this post.

    click here

    ReplyDelete
  5. Thanks for sharing, this is exactly what i was looking for.

    ReplyDelete