Cumulative Sum in SQL

For reporting purpose, some time we need to calculate the cumulative sum of a particular column or show running total of a numeric column of a table. It can be done easily by using SQL query. This article explains how to calculate running total in SQL query? Summary of the article:

• Cumulative Sum in SQL
• Output of Cumulative Sum

Cumulative Sum in SQL
Cumulative sum or running total is a common elements for any programmer. This is mainly used for reporting purpose. It can be done in different ways. Some reporting tools also provides mechanism for that. A simple but very efficient SQL statements given bellow that calculate cumulative sum of a table:

DECLARE @Temp TABLE (
	SL          INT,
	GroupName   NVARCHAR(100),
	Amount      DECIMAL(18,2)
	)

INSERT INTO @Temp VALUES(1,'Number-1',5)
INSERT INTO @Temp VALUES(2,'Number-2',8)
INSERT INTO @Temp VALUES(3,'Number-3',2)

SELECT      T1.SL,
            T1.GroupName, 
            T1.Amount, 
            SUM(T2.Amount) as CumulativeSum
FROM  @Temp T1 INNER JOIN 
            @Temp T2 on T1.SL >= T2.SL
GROUP BY T1.SL,T1.GroupName, T1.Amount
ORDER BY T1.SL

Lets have some discussion about the above query. At first we have declared a SQL table variable and insert some sample data. Then SQL INNER JOIN is used as self join. SQL GROUP BY ORDER BY clause is also used.

Output of Cumulative Sum
If we run the above code we will get the cumulative output.

That’s all about SQL cumulative sum.