Here’s a Quick Way To Generate a Running Total in SQL Server

Published on: 2017-07-18

SQL in 60 Seconds #2

Historically it’s been difficult to accomplish certain tasks in SQL Server.

Probably the most annoying problem I had to do regularly before SQL Server 2012 was to generate a running total. How can a running total be so easy to do in Excel, but difficult to do in SQL?

SUM(), click, drag, done. Excel, you will always have a place in my heart.

Before SQL Server 2012, the solution to generating a running total involved cursors, CTEs, nested subqueries, or cross applies. This StackOverflow thread has a variety of solutions if you need to solve this problem in an older version of SQL Server.

However, SQL Server 2012’s introduction of window functions makes creating a running total incredibly easy.

First, some test data:

CREATE TABLE dbo.Purchases
(
  CustomerID int,
  TransactionDate date,
  Price int
)
INSERT INTO dbo.Purchases VALUES (1,'2017-06-01',5)
INSERT INTO dbo.Purchases VALUES (1,'2017-06-15',8)
INSERT INTO dbo.Purchases VALUES (1,'2017-06-18',3)
INSERT INTO dbo.Purchases VALUES (1,'2017-06-30',6)
INSERT INTO dbo.Purchases VALUES (2,'2017-05-04',5)
INSERT INTO dbo.Purchases VALUES (2,'2017-06-04',5)
INSERT INTO dbo.Purchases VALUES (2,'2017-07-04',1)
INSERT INTO dbo.Purchases VALUES (3,'2017-05-01',2)
INSERT INTO dbo.Purchases VALUES (3,'2017-05-02',8)
INSERT INTO dbo.Purchases VALUES (3,'2017-05-03',9)
INSERT INTO dbo.Purchases VALUES (3,'2017-05-04',5)
INSERT INTO dbo.Purchases VALUES (3,'2017-05-05',4)
INSERT INTO dbo.Purchases VALUES (3,'2017-05-06',2)

Next, we write our query using the following window function OVER() syntax:

SELECT
  CustomerID,
  TransactionDate,
  Price,
  SUM(Price) OVER (PARTITION BY CustomerID ORDER BY TransactionDate) AS RunningTotal
FROM
  dbo.Purchases

The syntax for our OVER() clause is simple:

  • SUM(Price) specifies which column we are creating the running total on
  • PARTITION BY specifies around what group of data we want to create our “window” — each new window will reset the running total
  • ORDER BY specifies in what order the rows should be sorted before being summed

The results? An easy to write running total:

 

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

One thought on “Here’s a Quick Way To Generate a Running Total in SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.