SQL Server - Caluclate column sum on each Row

Running Total in SQL Server

In this article I'll explain how we can get running total on each row.

Let's take the example.

Step 1: Declare Sql Table

DECLARE @TempTbl TABLE  

(

  Id INT IDENTITY(1,1),

  Score INT

)

Step 2: Insert dummy records in the table

INSERT INTO @TempTbl(Score) VALUES(10)

INSERT INTO @TempTbl(Score) VALUES(20)

INSERT INTO @TempTbl(Score) VALUES(30)

INSERT INTO @TempTbl(Score) VALUES(40)

INSERT INTO @TempTbl(Score) VALUES(50)

Step 3: See the records in table

This is not mandatory step. However, check everything is fine.

SELECT * FROM @TempTbl



Check you table has above records.

Step 4: Write the query to get running total

There are many ways to get running total. I'll explain 3 ways.

1. With help of Sub query

     SELECT Id, Score,

(

   SELECT SUM(b.Score)FROM @TempTbl b 

   WHERE b.Id <= a.Id

 ) as Total

     FROM   @TempTbl a

2. With help of Join
     

    SELECT a.Id,a.Score,SUM(b.Score) as Total

    FROM   @TempTbl a, @TempTbl b

   WHERE b.Id <= a.Id

    GROUP BY  a.Id,a.Score

3. With help of Over (Only for SQLServer 2012 and above)

     SELECT a.Id, a.Score, SUM(a.Score) OVER (ORDER BY a.Id) as Total

     FROM   @TempTbl a

All 3 options will give the same output as shown in below screenshot. See the Total column, it is showing running total of Score column.



That is all about this article. I hope you like it.