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

--------------------------------------------------------------------------------------------------------