Watch this week's video on YouTube
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?
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:
