How to calculate running total in SQL Server?

To start with let us see what running total is. Running total is otherwise called a cumulative sum. It is the total of a value or multiple values in the current row and all the rows above it in a specific order. This summation is usually used to track the progression sum of values over time. In SQL server, there are several ways available to calculate running total from within a SELECT statement. Let us see a few of the common methods to calculate the running total.

To start with let us create a table and insert some sample records to it. The below table has 10 orders with the amount collected each day.

USE [MyTecBitsTestDB]
GO

-- Create Table
CREATE TABLE MTB_Orders
(
	OrderID INT PRIMARY KEY,
	OrderDate DATE,
	OrderAmount DECIMAL(10, 2)
);
GO

-- Insert Records
INSERT INTO MTB_Orders
	(OrderID, OrderDate, OrderAmount)
VALUES
	(1, '2024-01-01', 200),
	(2, '2024-01-02', 250),
	(3, '2024-01-03', 100),
	(4, '2024-01-04', 150),
	(5, '2024-01-05', 300),
	(6, '2024-01-06', 250),
	(7, '2024-01-07', 400),
	(8, '2024-01-08', 350),
	(9, '2024-01-09', 500),
	(10, '2024-01-10', 550);
GO

-- Check the inserted data
SELECT OrderID
      ,OrderDate
      ,OrderAmount
  FROM MTB_Orders
GO

1. Using window functions

A window function in SQL Server is a function that performs calculations across a set of rows related to the current row, within a result set. The term window refers to the group of rows within the database that the function will perform operations on. Window functions are defined using the OVER clause. Along with the OVER clause, we can use the aggregate window function SUM() to the running total. Here is the SQL statement to get the running total of our sample orders using window function:

SELECT
	OrderID,
	OrderDate,
	OrderAmount,
	SUM(OrderAmount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM MTB_Orders;
GO

2. Using a correlated sub-query

Using correlated sub-queries we can perform row-by-row summation of values. The correlated sub-query is executed once for every row of the outer query. This method is not a recommended method considering the performance. Here is the SQL statement to get the running total of our sample orders:

SELECT
	OrderID,
	OrderDate,
	OrderAmount,
	(
		SELECT SUM(O_2.OrderAmount)
		FROM MTB_Orders o_2
		WHERE o_2.OrderDate <= o_1.OrderDate) AS RunningTotal
FROM MTB_Orders o_1
ORDER BY OrderDate;
GO

3. Using CROSS APPLY

Another method is by using the CROSS APPLY operator. Here is the sample statement to get the running total of our orders table using CROSS APPLY:

SELECT
	o_1.OrderID,
	o_1.OrderDate,
	o_1.OrderAmount,
	RunningTotal
FROM MTB_Orders o_1
CROSS APPLY (
    SELECT SUM(o_2.OrderAmount) AS RunningTotal
	FROM MTB_Orders o_2
	WHERE o_2.OrderDate <= o_1.OrderDate
) rt;
GO

Reference

• More about OVER clause in SQL Server at Microsoft Docs.