How to Calculate Running Total in SQL

A running total (also known as cumulative total) is the sum of a value that grows as you move row by row in a specific order. It’s widely used in financial reports, sales tracking, and analytics.

In SQL, the best way to calculate running totals is by using window functions like SUM() OVER (...).

Follow the steps below to calculate the running total in SQL.

Step-1: Create a Sample Table

Let’s create a simple Sales table to demonstrate:

CREATE TABLE Sales (
OrderID INT PRIMARY KEY,
OrderDate DATE,
Region VARCHAR(50),
SalesAmount DECIMAL(10,2)
);

Step-2: Insert Sample Data

Here’s some sample data we will use:

INSERT INTO Sales (OrderID, OrderDate, Region, SalesAmount) VALUES
(1, '2024-01-01', 'East', 200.00),
(2, '2024-01-02', 'East', 300.00),
(3, '2024-01-05', 'East', 150.00),
(4, '2024-01-01', 'West', 400.00),
(5, '2024-01-03', 'West', 250.00),
(6, '2024-01-04', 'West', 500.00);

Step-3: Running Total by Region

SELECT 
Region,
OrderID,
OrderDate,
SalesAmount,
SUM(SalesAmount) OVER (
PARTITION BY Region
ORDER BY OrderDate
) AS RunningTotal
FROM Sales;

Summary Point:

The SUM() OVER (ORDER BY …) function is the easiest and most powerful way to calculate running totals in SQL. By using PARTITION BY, you can calculate separate running totals for different groups, such as Region, Customer, or Product.

Thanks for reading this post! I hope you found it helpful. Feel free to share it with others or your teammates so they can benefit from it too.