MS SQL Server - Calculate Running Total

In this post we will see different ways to calculate running total using T-SQL. I am using Microsoft SQL SERVER 2008 R2. Let first setup a target table we want to calculate running values. Create a new table (say @tblItems) with the following script.

 declare @tblItems table(
  ID int identity(1, 1),
  Amount decimal(12, 3),
  RunningTotal decimal(12, 3) default(0)
 )

Populate @tblItems table with some dummy records.

 insert into @tblItems(Amount)
 values
  (50),
  (20),
  (100),
  (430),
  (80)
 ;

SELF JOIN

This is the first way we are calculating running total using INNER JOIN.

 SELECT t1.ID, t1.Amount, RunningTotal = SUM(t2.Amount)
 FROM @tblItems AS t1
 INNER JOIN @tblItems AS t2
   ON t1.ID >= t2.ID
 GROUP BY t1.ID, t1.Amount
 ORDER BY t1.ID;

But this technique is not recommended for large tables, because as the table gets larger, each incremental row requires reading n-1 rows in the table. This may lead to failures, timeouts, or low performance.

Update with Local Variable:

 DECLARE @RunningTotal decimal(12, 3) = 0;

 UPDATE @tblItems
 SET @RunningTotal = RunningTotal = @RunningTotal + Amount
 FROM @tblItems;

 SELECT ID, Amount, RunningTotal
 FROM @tblItems
 ORDER BY ID;

This method is more efficient than the above, but since the behavior is not properly documented, there are no guarantees about order.

Nested / Sub-Query

 SELECT ID, Amount,
  (SELECT SUM(amount) 
   FROM @tblItems t2 WHERE t2.ID <= t1.ID
          ) 
  as RunningTotal 
    
 FROM @tblItems as t1

This method performs similar to self join technique, as the table gets larger, this may lead to failures, timeouts, or low performance.

OVER Clause (For SQL Server 2012 or above)

New window functions introduced in SQL Server 2012 make this task a lot easier.

 SELECT ID, Amount, 
   SUM(Amount) OVER (ORDER BY ID) as RunningTotal 
 FROM @tblItems 

It performs better than all of the above techniques.