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.