Easiest Way of Calculating Running Total in SQL Server with SUM() Window Function

In this post, we'll go over examples of calculating running total using SUM() Window Function.

Using Window Function is the easiest and fastest way to calculate running total in SQL Server as it is built-in and designed for this analytical purpose.

You can use this DB Fiddle to follow along and calculate Running Total with SUM() Window Function.

Sample Data for Running Total Calculation

Let’s create some sample data to demonstrate how to calculate running total using SUM() Window Function.

We will create a EmployeeSales table which has a EmployeeID, SaleDate, and SaleAmountcolumns. SaleAmount stores each employee's daily sales amount.

Create EmployeeSales Table

CREATE TABLE EmployeeSales(
  [EmployeeID] INT,
  [SaleDate] DATE,
  [SaleAmount] INT
)

INSERT INTO EmployeeSales VALUES
(1,'2022-06-01',10),
(1,'2022-06-02',20),
(1,'2022-06-03',30),
(2,'2022-06-01',10),
(2,'2022-06-02',10),
(2,'2022-06-03',10)

EmployeeID	SaleDate	SaleAmount
1	2022-06-01	10
1	2022-06-02	20
1	2022-06-03	30
2	2022-06-01	10
2	2022-06-02	10
2	2022-06-03	10

Calculate Running Total with SUM() Window Function

The window function SUM() OVER makes generating a running total a very easy task.

We start by partitioning data by EmployeeID and order data within each partition by SaleDate.

Then SUM(ES.SaleAmount) aggregate function gets applied for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to compute running total for each row.

Calculate Running Total of SaleAmount for Each Employee

SELECT *
	,SUM(ES.SaleAmount) OVER (
		PARTITION BY ES.EmployeeID ORDER BY ES.SaleDate
		ROWS BETWEEN UNBOUNDED PRECEDING
				AND CURRENT ROW
		) AS RunningTotal
FROM EmployeeSales AS ES

info

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the default behaviour for SUM() so we could omit this clause and still get the same results.

EmployeeID	SaleDate	SaleAmount	RunningTotal
1	2022-06-01	10	10
1	2022-06-02	20	30
1	2022-06-03	30	60
2	2022-06-01	10	10
2	2022-06-02	10	20
2	2022-06-03	10	30

Let's try to calculate running total of entire dataset starting with the earliest SaleDate.

Calculate Running Total of Entire Dataset

SELECT *
	,SUM(ES.SaleAmount) OVER (
		ORDER BY ES.SaleDate
		) AS RunningTotal
FROM EmployeeSales AS ES

info

Note that we omit PARTITION BY clause in this case as we're calculating running total for the entire dataset.

You will notice that row 1 (should be 10), row 3 (should be 30), and row 5 (should be 80) have incorrect RunningTotal value.

Why is this happening?

This is because we only specify SaleDate in the ORDER BY clause which results in duplicate values.

For example, rows 1 and 2 have the same SaleDate 2022-06-01. Rows 3 and 4 have the same SaleDate 2022-06-02

EmployeeID	SaleDate	SaleAmount	RunningTotal
1	2022-06-01	10	20
2	2022-06-01	10	20
2	2022-06-02	10	50
1	2022-06-02	20	50
1	2022-06-03	30	90
2	2022-06-03	10	90

We can fix this by providing a combination of columns to ORDER BY clause which can generate unique combined value for each row.

We know that any combination of EmployeeID and SaleDate is unique so we can specify these 2 columns in the ORDER BY clause.

We now have the correct progression of the running total value.

Any Combination of EmployeeID and SaleDate Is Unique

SELECT *
	,SUM(ES.SaleAmount) OVER (
		ORDER BY ES.SaleDate
			,ES.EmployeeID
		) AS RunningTotal
FROM EmployeeSales AS ES

EmployeeID	SaleDate	SaleAmount	RunningTotal
1	2022-06-01	10	10
2	2022-06-01	10	20
1	2022-06-02	20	40
2	2022-06-02	10	50
1	2022-06-03	30	80
2	2022-06-03	10	90

info

Window Function is only available in SQL Server 2012 or later. If you're running an older version of SQL Server, I recommend using self join technique to calculate running total.