Wednesday, August 7, 2013

Cumulative Sum in SQL




Running Total in SQL

Cumulative Sum in SQL

Some time we need to do cumulative sum or running total in a table. In SQL we can easily do it. The following code will help us.


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


If we run the above code we will get the cumulative output.

No comments:

Post a Comment