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