Dec 9, 2012

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
OUTPUT

    Choose :
  • OR
  • To comment
No comments:
Write Comments