Calculate Running Total with Correlated Subquery in SQL Server

In this post, we'll go over an example of calculating running total of each employee's daily sales using a correlated subquery.

You can use this DB Fiddle to follow along and calculate Running Total with Subquery.

Sample Data for Running Total Calculation

Let’s create some sample data to demonstrate how to calculate running total using correlated subquery.

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 Subquery

It's common to come across SQL codes calculating a running total using a subquery.

For every row in the original query, a correlated subquery is executed to compute the running total of the EmployeeID up to the SaleDate.

Calculate Running Total with Subquery
SELECT *
	,(
		SELECT SUM(SQ.SaleAmount)
		FROM EmployeeSales AS SQ
		WHERE SQ.EmployeeID = ES.EmployeeID
			AND SQ.SaleDate <= ES.SaleDate
		) AS RunningTotal
FROM EmployeeSales AS ES

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

The code is easy to understand, but the subquery forces the query to scan the table for every row that exists in the original table.

This approach is usually fine when dealing with a fairly small dataset. But it's not recommended if you're dealing with a larger dataset as you might run into performance issues.

Therefore, you should consider using Self Join to compute running total or utilizing Window Function to calculate running total.

Sample Data for Running Total Calculation​

Calculate Running Total with Subquery​

Sample Data for Running Total Calculation

Calculate Running Total with Subquery