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
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

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

If you are running version prior to SQL Server 2012, refer below posts
Cumulative Sum in Sql Server 2012 Cumulative Sum in Sql Server 2012 Reviewed by Sandeep Mittal on Sunday, December 09, 2012 Rating: 5

No comments:

Powered by Blogger.