How to Calculate Running Total in SQL Server?

Calculating the Running total in SQL Server is essential for cumulative sum calculations in reports, financial analysis, and trend tracking. Adding values row by row based on a specific order, such as transactional IDs, helps to maintain an accurate cumulative total. In this blog, let us explore the different methods to calculate the running total in SQL Server in detail with examples for each.

Table of Contents:

Why is it necessary to compute the SQL Server Running Total?

Finding the running total in SQL Server makes it easier to spot patterns and trends in the datasets and helps with decision-making by enabling the tracking of progressive totals over time. These concepts are mainly used in business intelligence and performance analysis to compare the calculated values with the targets. Using SQL-based solutions ensures efficient, scalable, and real-time calculations within the database

Methods to Calculate the Running Total in SQL Server

Before getting started with the methods, let us create a Transactional Log table and insert values into it so that it can be used as an example for the following methods

CREATE TABLE Tran_Log (
    Tran_ID INT PRIMARY KEY IDENTITY(1,1),
    EventSeq INT,
    Tran_Value DECIMAL(10,2)
);
INSERT INTO Tran_Log (EventSeq, Tran_Value) VALUES
(1, 120.00),
(2, 180.50),
(3, 210.75),
(4, 260.00),
(5, 300.25);
SELECT * FROM Tran_Log;

This is how the table looks once it is created and the values are inserted.

Method 1: Using SUM() with OVER(ORDER BY) Clause in SQL Server

The most effective way for calculating a running total is utilizing Windows functions, which do not require subqueries or JOINS

Syntax:

SELECT 
    col1, 
    col2, 
    SUM(aggregate) OVER (ORDER BY colname) 
FROM table;

Example:

SELECT 
    Tran_ID,
    EventSeq,
    Tran_Value,
    SUM(Tran_Value) OVER (ORDER BY EventSeq) AS AccumulatedValue
FROM Tran_Log;

Output:

Explanation: Here, the cumulative total is taking place from row to row using the SUM(Tran_Value) OVER (ORDER BY EventSeq) function

Method 2: Using Correlated Subquery in SQL Server

A correlated subquery derives the running total based on all previous values in the data set before the current record.

Syntax:

SELECT 
    main_table.column1, 
    main_table.column2, 
    (SELECT SUM(sub_table.aggregate_column) 
     FROM table_name sub_table 
     WHERE sub_table.order_column <= main_table.order_column) 
FROM table_name main_table
ORDER BY main_table.order_column;

Example:

SELECT 
    TL_A.Tran_ID,
    TL_A.EventSeq,
    TL_A.Tran_Value,
    (
        SELECT SUM(TL_B.Tran_Value)
        FROM Tran_Log TL_B
        WHERE TL_B.EventSeq <= TL_A.EventSeq
    ) AS AggregatedTotal
FROM Tran_Log TL_A
ORDER BY TL_A.EventSeq;

Output:

Explanation: Here, the subquery runs for each row in TL_A, which sums up all the transactional values where TL_B.EventSeq is less than or equal to TL_A.EventSeq.

Method 3: Using CTE with SUM() in SQL Server

A Common Table Expression is a temporary result set in a structured format. Instead of using a subquery for each row, we can calculate the total by using either a recursive CTE with the SUM() function

Syntax:

WITH CTE AS (
    SELECT 
        colA, 
        colB, 
        SUM(column) OVER (ORDER BY colm_name) AS Total
    FROM table_name
)
SELECT * FROM CTE;

Example:

WITH EventTotals AS (
    SELECT 
        Tran_ID,
        EventSeq,
        Tran_Value,
        SUM(Tran_Value) OVER (ORDER BY EventSeq) AS CTERunningTotal
    FROM Tran_Log
)
SELECT * FROM EventTotals;

Output:

Explanation: Here, the CTE stores the intermediate results, and the SUM(Tran_Value) OVER (ORDER BY EventSeq) function makes sure that each row accumulates all of the previous rows and is displayed.

Method 4: Using Variables in the SELECT Statement in SQL Server

Using Variables in the SELECT Statement assigns a variable within the execution that continues to update as each row is processed sequentially.  

Syntax:

DECLARE @RunningTotal DECIMAL(10,2) = 0;
SELECT 
    column1, 
    column2, 
    SET @RunningTotal = @RunningTotal + aggregate_column 
FROM table_name
ORDER BY order_column;

Example:

DECLARE @CumulativeSum DECIMAL(10,2) = 0;
SELECT 
    Tran_ID,
    EventSeq,
    Tran_Value,
    RunningTotal = CAST(0 AS DECIMAL(10,2)) 
INTO #TempRunningTotal
FROM Tran_Log
ORDER BY EventSeq;
UPDATE #TempRunningTotal
SET @CumulativeSum = RunningTotal = @CumulativeSum + Tran_Value;
SELECT * FROM #TempRunningTotal;
DROP TABLE #TempRunningTotal; 

Output:

Explanation: Here, the @CumulativeSum variable is declared to store the running total, and the running total is updated row by row by adding SalesAmount with the UPDATE Statement.

Alternative Approaches for Calculating the Running Total 

There are multiple ways to determine the running total, such as using Windows functions in MySQL or using the Recursive CTE in SQLite.  

Method 1: Using the Windows Function in MySQL

This method uses Windows functions to get the cumulative sums without requiring subqueries or JOINS.

Syntax:

SELECT 
    col1, 
    col2, 
    SUM(aggregate) OVER (ORDER BY colname) 
FROM table;

Example:

CREATE TABLE Tran_Log (
    Tran_ID INT PRIMARY KEY AUTO_INCREMENT,
    EventSeq INT,
    Tran_Value DECIMAL(10,2)
);
INSERT INTO Tran_Log (EventSeq, Tran_Value) VALUES
(1, 130.00),
(2, 150.50),
(3, 280.75),
(4, 278.00),
(5, 301.25);
SELECT 
    EventSeq, 
    Tran_Value, 
    SUM(Tran_Value) OVER (ORDER BY EventSeq) AS Running_Total
FROM Tran_Log;

Output:

Explanation: Here, the cumulative total is taking place from row to row using the SUM(Tran_Value) OVER (ORDER BY EventSeq) function

Method 2: Using Recursive CTE in SQLite

A recursive common table expression (CTE), which allows you to reference the same CTE multiple times, is useful for executions that involve running totals.

Syntax:

WITH RECURSIVE CTE AS (
    SELECT KeyID, Value 
    FROM YrTab
    WHERE KeyID = (SELECT MIN(KeyID) FROM YourTable)
    UNION ALL
    SELECT yt.KeyID, yt.Value, pr.Cumulative + yt.Value
    FROM YrTab yt
    JOIN pr ON yt.KeyID = pr.KeyID + 1
)
SELECT * FROM tab_name;

Example:

CREATE TABLE Tran_Log (
    Tran_ID INTEGER PRIMARY KEY AUTOINCREMENT,
    EventSeq INTEGER,
    Tran_Value DECIMAL(10,2)
);
INSERT INTO Tran_Log (EventSeq, Tran_Value) VALUES
(1, 120.00),
(2, 180.50),
(3, 210.75),
(4, 260.00),
(5, 300.25);
WITH RECURSIVE Log_Tracker AS (
    SELECT Tran_ID, EventSeq, Tran_Value, Tran_Value AS RunningTotal
    FROM Tran_Log
    WHERE Tran_ID = (SELECT MIN(Tran_ID) FROM Tran_Log)
    UNION ALL
    SELECT t.Tran_ID, t.EventSeq, t.Tran_Value, lt.RunningTotal + t.Tran_Value
    FROM Tran_Log t
    JOIN Log_Tracker lt ON t.Tran_ID = lt.Tran_ID + 1
)
SELECT * FROM Log_Tracker;

Output:

Explanation: Begin with the initial transaction using MIN(Tran_ID) for the running total, and then add each subsequent Tran_Value recursively to the previous total through the join on Tran_ID +1.

Performance Comparison of Each Method

Method	Use Case	Pros	Cons
SUM() with OVER (ORDER BY)	Used for sequential data with large datasets	Optimized for running totals	Not supported in older SQL versions
Correlated Subquery	Used when working with small datasets	Easy to implement	Poor performance due to row-by-row execution
CTE with SUM()	Used when additional transformations are needed	Easy to read and implement	CTEs may impact performance if materialized multiple times in large queries
Variables in the SELECT statement	When the order is predefined, it works well	Handles smaller datasets efficiently	Does not handle parallel execution

Real-world Examples

1. Amount Tracking System

A company wants to track the total amount that is spent over time.

Example:

CREATE TABLE Bud_Spend (
    DeptID INT,
    SpendDt DATE,
    AmountSpent DECIMAL(10,2)
);
INSERT INTO Bud_Spend VALUES
(1, '2024-01-10', 1000),
(1, '2024-03-15', 1500),
(1, '2024-05-20', 1300),
(2, '2024-02-05', 2000),
(2, '2024-04-18', 1800),
(2, '2024-06-25', 1600);
SELECT 
    DeptID,
    SpendDt,
    AmountSpent,
    SUM(AmountSpent) OVER (PARTITION BY DeptID ORDER BY SpendDt) AS TotalSpend
FROM Bud_Spend;

Output:

Explanation: Here, the total amount spent can be calculated according to the SpendDt using the SUM(AmountSpent) OVER (PARTITION BY DeptID ORDER BY SpendDt) function.

2. Score Card Tracking

A school wants to keep track of the scores of a player in a cricket match.

Example:

CREATE TABLE Play_Sco (
    Ply_ID INT,
    Ply_Dt DATE,
    Score INT
);
INSERT INTO Play_Sco VALUES
(10, '2024-01-05', 50),
(10, '2024-01-10', 70),
(10, '2024-01-15', 80),
(11, '2024-02-01', 60),
(11, '2024-02-08', 90),
(11, '2024-02-15', 75);
SELECT 
    Ply_ID,
    Ply_Dt,
    Score,
    SUM(Score) OVER (PARTITION BY Ply_ID ORDER BY Ply_Dt) AS RunningScore
FROM Play_Sco;

Output:

Explanation: Here, the overall score of a player is calculated using the SUM(Score) OVER (PARTITION BY Ply_ID ORDER BY Ply_Dt) function.

Best Practices

• Use window functions: Window functions reduce execution time because they perform better than correlated subqueries and cursors.
• Avoid Cursors with Large Data Sets: Cursors go row by row, which leads to slow performance. Cursors should be avoided with large datasets; rather, explore set-based solutions, including CTE or window functions.
• Index Properly: If you add an index on the columns, performance, especially with large datasets, is positively impacted.

Conclusion

The most effective way to calculate the total is to use the Windows function; however, depending on the use case and performance, other approaches such as CTE, Correlated subqueries, and variables can also be used. In this blog, you have gained knowledge of different methods to calculate the running total in SQL Server.

Take your skills to the next level by enrolling in our SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.

Calculate Running Total in SQL Server – FAQs