Microsoft SQL Server ResourcesCalculate Running Totals and Sums on Microsoft SQL Server Tables

Provided by Michelle Swann-Renee, EVP Consulting Services

Calculating the running total or sum in a table is not a built in feature of Microsoft SQL Server. Here's some code for a stored procedure to generate this easily within your SQL Server database.

Sample Table: OrderDetails

OrderID ProductID Amount OrderDate
1 5 5 3/1/2017
2 7 20 3/12/2017
3 7 7 3/15/2017
4 5 12 3/21/2017
5 2 3 3/29/2017
6 5 6 4/1/2017
7 2 9 4/5/2017

Stored Procedure for Calculating Running Sum

Here is the stored procedure to obtain a running sum of Amount by ProductID for the sample table. Just execute the following SQL statement:

SELECT ProductID, OrderDate, Amount, 
  (SELECT Sum(o.Amount) FROM OrderDetails o 
   WHERE o.ProductID = OrderDetails.ProductID AND o.OrderDate <= OrderDetails.OrderDate)
AS RunningSum
ORDER BY ProductID, OrderDate

The results are:

ProductID OrderDate Amount RunningSum
2 3/29/2017 3 3
2 4/5/2017 9 12
5 3/1/2017 5 5
5 3/21/2017 12 17
5 4/1/2017 6 23
7 3/12/2017 20 20
7 3/15/2017 27 27

Go to the tips page.

Free Product Catalog from FMS