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 Do You Need to Calculate Running Total in SQL Server?
• Top Methods to Calculate Running Total in SQL Server
  • Method 1: Running Total with SUM() and OVER() in SQL Server
  • Method 2: Running Total Using Correlated Subquery in SQL Server
  • Method 3: Calculate Running Total Using CTE and SUM() in SQL Server
  • Method 4: Using SQL Variables to Track Running Total in SQL Server
• Alternate Approaches for Running Totals in Other SQL Platforms
  • Method 1: Using the Windows Function in MySQL
  • Method 2: Using Recursive CTE in SQLite
• Performance Comparison of Each Method
• SQL Server Version Compatibility for Each Method
• SQL Server vs MySQL vs SQLite for Running Totals
• Use Cases of Running Total in Business Analytics
• Limitations of Using Variables for Running Total SQL
• Real-World Use Cases of SQL Server Running Totals
• Best Practices to Calculate Running Total in SQL Server
• Conclusion

Why Do You Need to Calculate Running Total in SQL Server?

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

Top Methods to Calculate 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 best method for running total in SQL Server.

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: Running Total with SUM() and OVER() in SQL Server

The most effective way for calculating a SQL server 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: Running Total Using Correlated Subquery in SQL Server

A correlated subquery derives the SQL Server 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: Calculate Running Total Using CTE and 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 SQL Server 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 SQL Variables to Track Running Total 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,
    @RunningTotal = @RunningTotal + aggregate_column AS RunningTotal
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 SQL Server running total, and the running total is updated row by row by adding SalesAmount with the UPDATE Statement.

Alternate Approaches for Running Totals in Other SQL Platforms

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 SQL 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

SQL Server Version Compatibility for Each Method

SQL Server vs MySQL vs SQLite for Running Totals

Use Cases of Running Total in Business Analytics

Running totals are a critical feature in business analytics, enabling continuous tracking and evaluation of key performance metrics over time. Here are several practical scenarios:

Financial Forecasting & Budget Tracking

• A company monitors cumulative expenses or revenue month-over-month.
• With a running total SQL query, leaders can quickly compare actual performance against budget forecasts and detect trends.

Sales Trend Analysis

• Sales teams track daily or weekly revenue accumulation.
• Using SQL Server sum and SQL window function methods allows transparent identification of rising or falling sales momentum.

Inventory Movement Monitoring

• Businesses can track a product’s inventory levels by calculating cumulative inflows and outflows.
• Accurate running total SQL aggregation prevents stockouts and informs ordering strategies.

Customer Engagement and Loyalty Programs

• Organizations accumulate customer activity points over time through purchases, visits, and engagements.
• SQL cumulative sum functions calculate total points, supporting tier-based rewards and personalized marketing campaigns.

Performance Scorecards and Dashboards

• Analysts compile scorecards where each metric like clicks, subscriptions, or defects is listed cumulatively.
• Running totals in these dashboards deliver insight into whether targets are being met and when milestones occur.

Limitations of Using Variables for Running Total SQL

Sequential Processing Only
Variables update row-by-row, disallowing parallel execution that will slow down computations on large tables.

Unreliable Ordering
Unless results are explicitly ordered, assigning based on variable progression can produce unpredictable running totals.

Non-deterministic Behavior
Variable-based calculations may vary across runs or sessions, especially when query plans differ.

Poor Integration in Complex Queries
Variables are hard to nest within JOINs, GROUP BYs, or subqueries, limiting their flexibility.

Debugging Difficulties
It’s challenging to trace each row’s update in a large result set, complicating validation.

Real-World Use Cases of SQL Server Running Totals

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 to Calculate Running Total in SQL Server

• Use window functions: SQL 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.

The following resources provide a comprehensive guide to using SQL Server as a data source for Power BI.

Web Scraping in Power BI – Connect Power BI to a webpage for real-time data scraping.

Power BI Pie Chart – Best practices for pie charts in Power BI reports.

Bar and Column Charts in Power BI – Visualizing categorical data using bar charts in Power BI.

Join Tables in Power BI – Power BI table joins: inner, outer, and more.

Calculate Running Total in SQL Server – FAQs