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 '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 Multiple 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 :



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


3) 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 Multiple 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 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