Calculate cumulative sum of previous rows in SQL Server

This is a very interesting and tricky interview question. Calculate a running total or cumulative sum of previous rows? I have explained the answer using different methods like recursive CTE, incremental variable, subquery and widow function.

This is how the original table looks like..

Id Sub Marks
1 English 60
2 Maths 75
3 Science 90
4 SS 80
5 Hindi 45
6 Gujarati 55
7 Moral Science 65
8 Home Science 85

And, we want a result set like below..

Id Sub Marks Total
1 English 60 60
2 Maths 75 135
3 Science 90 225
4 SS 80 305
5 Hindi 45 350
6 Gujarati 55 405
7 Moral Science 65 470
8 Home Science 85 555

The “Total” column is a cumulative sum of “Marks” column in above table. Now we need to write down a query to have a same result like above.

Solution 01: (Using sub query)

Solution 02: (Using an auto increment variable)

Solution 03: (Using widow function)

Know more about widow functions in SQL Server

Solution 04: (Using recursive CTE)

Know more about recursive CTE in SQL Server

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

2 thoughts on “Calculate cumulative sum of previous rows in SQL Server

  • March 5, 2016 at 1:44 pm
    Permalink

    In 2012 very easy to calculate Cumulative Sum

    try this

    SELECT
    Id,Sub, Marks,
    ,SUM(Marks) OVER(ORDER BY Id)
    FROM @tableMarks

    Reply
  • May 18, 2017 at 8:17 pm
    Permalink

    Hi,
    There is a consecutive stock I want to make and I want to fall out of these stocks, and in some cases stocks can go down and I hear that the next stake goes down to zero when it goes down.

    I tried the examples 3 and 4 in your example and I experienced the same problem in 2 cases.

    DECLARE @tableMarks TABLE
    (Id INT identity(1,1),
    Stock int,
    OrderTotal INT,
    Total int)

    INSERT INTO @tableMarks(Stock,OrderTotal)
    SELECT -20,60 UNION ALL
    SELECT 100 ,75 UNION ALL
    SELECT 90,0 UNION ALL
    SELECT 90, 0 UNION ALL
    SELECT 10, 500 UNION ALL
    select 5000,0 UNION ALL
    select -200,100

    SELECT Id,Stock ,OrderTotal,
    –SUM(Stock-OrderTotal) OVER(ORDER BY Id
    SUM(case when Stock-OrderTotal<0 then 0 else Stock-OrderTotal end ) OVER(ORDER BY Id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS Total
    FROM @tableMarks

    Reply

Leave a Reply

%d bloggers like this: