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

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