Calculate 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: