Friday, 15 December 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
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts