Sunday, December 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. 

Please note the below solution will work only in Sql Server 2012 onwards only.

If you are running prior versions, refer related posts
Table & Data Script
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
select * from Marks
OUTPUT

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

If you are running version prior to SQL Server 2012, refer below posts

0 comments:

Post a Comment