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.

Strategic Overview

SQL Server Express

SQL Azure Cloud

What it Means to Information Workers

Link Microsoft Access to SQL Azure Databases

Deploy MS Access DBs linked to SQL Azure

Eliminate SQL Azure Security Holes

Monitor SQL Server Usage and DTU Limits on Azure

Convet Azure SQL Server to Elastic Pools

SQL Server Tips

Database Users and Permissions

Database Backup and Restore on Network

AM/PM Date Time Format

Get Date Portion

Set NOCOUNT ON

Running Total

Videos

Migrate Your Data Tier to SQL Server: Strategies for Survival

Microsoft Access Database and Migration Challenges

Are we there yet? Successfully navigating the bumpy road from Access to SQL Server

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Services

Microsoft SQL Server Consulting Services

Visual Studio .NET programmers for web development

General Microsoft Access consulting services

Additional Resources

Microsoft Access Help

Microsoft Access Developer Help Center

MS Access Developer Programming

More Access and SQL Tips

Technical Papers

Microsoft Access Tools

Connect with Us

 

Free Product Catalog from FMS