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.