Friday, December 15, 2017

How to Calculate the Cumulative Sum or Running Total in SQL Server

SQL Query to Calculate the Cumulative Sum or Running Total
Scenario :
Suppose we have a Table "TblSample" as follows..


Now we can Calculate the Cumulative Sum or Running Total on this Table in various ways.
1) Using Sum () Function with Over () Clause :
This is the simplest method to calculate the cumulative sum/running total in SQL Server.
A) Cumulative Sum based on Id Column :
     Select *, Sum(NetSales) Over ( Order by ID ) As CumSum_ById
     From [dbo].[TblSample]
Result :
B) Cumulative Sum based on Id Column and Partition By Region: 
    Select *, Sum(NetSales) 
                  Over ( Partition By Region Order By ID ) As CumSum_ById_Region
     From [dbo].[TblSample]
Result :

2) Using 'Correlated Scalar Query' :
A)  Cumulative Sum based on Id Column
      Select * , (  Select Sum(NetSales) From [TblSample] S2
                        Where S2.ID<= S1.ID
                      )  Cumulative_Net_ById
      From [dbo].[TblSample] S1
     
Order BY S1.ID
     GO
Result :
B Cumulative Sum based on Partition By specific Columns
     Select * , (  Select Sum(NetSales) From [TblSample] S2
                   Where S2.ID<= S1.ID
                )  Cumulative_Net_ById,
                (  Select Sum(NetSales) From [TblSample] S2
                   Where S2.ID<= S1.ID and
                   S2.Region=S1.Region
                )  Cumulative_Net_ByRegion,

               (  Select Sum(NetSales) From  [TblSample] S2
                  Where S2.ID<= S1.ID and
                  S2.Region=S1.Region and
                  S2.Product=S1.Product
               ) 
Cumulative_Net_ByProduct
      From [dbo].[TblSample] S1
      Order BY S1.ID,S1.Region,S1.Product
      GO
Result :


3) Using 'Self Join Query' : 

Cumulative Sum based on Id Column
Select S1.ID,S1.Region,S1.Product,S1.QTR,S1.NetSales,
           Sum(S2.NetSales)Cumulative_Net
      
From  [DimSample] S1,
                 [DimSample]
S2
       Where S2.ID<= S1.ID
Group By S1.ID,S1.Region,S1.Product,S1.QTR,S1.NetSales
Order BY S1.ID


4) Using 'Common Table Expressions' : 
Suppose if we don't have 'ID' Column in the Table , then we can generate the 'ID' Column using the Common Table Expressions (CTE) , and the we can Calculate the Cumulative Sum or Running Total as follows..
Select * From [dbo].Tbl_Sample
Calculating Cumulative Sum based on Partition By specific Columns using Common Table Expressions (CTEs) :
WITH S1 AS  '--First CTE
 (
   SELECT Row_Number() Over (Order By Region) RowId,* FROM [Tbl_Sample]
 ),
 S2  AS  '--Second CTE
 (
   SELECT Row_Number() Over (Order By Region) RowId,*FROM [Tbl_Sample]
 )
Select * , (  Select Sum(NetSales) From S2
       Where S2.RowId<= S1.RowId
    )  Cumulative_Net_ById,

    (  Select Sum(NetSales) From S2
       Where S2.RowId<= S1.RowId and
       S2.Region=S1.Region
    )  Cumulative_Net_ByRegion,

    (  Select Sum(NetSales) From S2
       Where S2.RowId<= S1.RowId and
       S2.Region=S1.Region and
       S2.Product=S1.Product
    ) 
Cumulative_Net_ByProduct 

From S1
Order BY S1.RowId,S1.Region,S1.Product

GO       

Result :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

2 comments:

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.