Cumulative Sum in Sql Server 2012

In this article, I am sharing how cumulative total can be calculated very easily in SQL Server 2012.

Note: Solution will work in Sql Server 2012 onward.

For prior versions, refer related posts

Let's create Marks Table and populate some data in it
create table Marks(
     studid  VARCHAR(20),
     subcode VARCHAR(20),
     marks   INT
insert into Marks
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

In SQL Server 2012, with the expansion of the OVER clause to include ORDER BY support with aggregates, it becomes very easy to calculate cumulative sum in SQL Server

Query to calculate cumulative sum for all subjects student wise
SELECT *, SUM(marks) OVER(PARTITION BY studid ORDER BY subcode) [Cumulative Sum]
FROM   Marks

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Next Post »