T-SQL - CUMULATIVE SUM

T-SQL   -  CUMULATIVE  SUM 

There are several ways to calculate sum in T – SQL

Here, I will show how to calculate cumulative sum in T-Sql using row_number function.

Let’s take an example where we have to calculate student wise cumulative sum of marks.

Script to create a table:

CREATE TABLE #tblMarks(

    studid  VARCHAR(20)

    , subcode VARCHAR(20)

    , marks   INT

)

 

 

Populating Data into the table:

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

 

Retrieving Data from the table #tblMarks

 

SELECT * FROM #tblMarks

 

 

 

 

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 :