In this very brief tutorial, we’ll discuss how to gather a very particular kind of SQL data: A running total.
The best way I found to gather a running total (sometimes called a rolling total) of SQL data is to…
Use the SUM() aggregate window function
There might be other ways to gather a running total, but I have found that the easiest way is to use the SUM aggregate in a window function.
If you need a quick rundown on what a window function is in the first place, check out the full beginner-friendly tutorial:
SQL Server Window Functions: An introduction for beginnersÂ
Let’s take a look at an example.
Setting up some data:
We’ll set up three tables to demonstrate how to gather a running total of your data. We’ll create three tables: Customers, Products, and Orders.
CREATE TABLE Customers(
CustID int IDENTITY(50,5) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NULL
)
CREATE TABLE Products(
ProductID int IDENTITY(20,2) NOT NULL,
ProductName varchar(20) NULL,
Price decimal(5, 2) NULL
)
CREATE TABLE Orders(
OrderID int IDENTITY(100,10) NOT NULL,
CustID int NULL,
ProdID int NULL,
Qty tinyint NULL,
Orderdate datetime NULL
)
INSERT Customers (FirstName, LastName)
VALUES
('Joshua', 'Porter'),
('Andrew', 'Bluefield'),
('Jack', 'Donovan'),
('Cindy', 'Thatcher'),
('Gordon', 'Acres'),
('Gretchen', 'Hamilton')
INSERT Products (ProductName, Price)
VALUES
('Large Bench', 198.00),
('Small Bench', 169.40),
('Coffee Table', 220.00),
('Side Tables', 265.20),
('Coat Rack', 45.00)
INSERT Orders (CustID, ProdID, Qty, Orderdate)
VALUES
(55, 22, 1, '2021-06-01'),
(60, 28, 2, '2021-06-06'),
(75, 26, 1, '2021-06-13'),
(50, 20, 1, '2021-07-01'),
(55, 28, 1, '2021-07-06'),
(65, 24, 1, '2021-07-14'),
(55, 26, 1, '2021-07-18'),
(50, 26, 1, '2021-07-24'),
(70, 24, 1, '2021-08-06'),
(70, 26, 1, '2021-08-06'),
(70, 22, 3, '2021-09-01')
The Orders table contains foreign key links to the Customers and Products tables to tell us who made the order and what they purchased. We also see the date the order was placed.
This is what the data should look like:
Gathering a running total:
Let’s say we wanted to see our data presented in order by OrderDate, and we wanted to see how much money we have made in total up to that point in time. I.E. We want to see a running total.
First, we’ll write a quick query that uses JOINs to see the following details:
- Who made the order and when
- The price of the product they ordered
- The final sale price, which is the price multiplied by the quantity:
Here’s that query for you to copy and paste:
SELECT O.OrderID, O.Orderdate, C.FirstName, C.LastName, P.ProductName, P.Price as 'Price of Product', O.Qty as 'Qty Purchased', P.Price * O.Qty as 'Sale Price' FROM Orders as O INNER JOIN Customers as C ON O.CustID = C.CustID INNER JOIN Products as P ON O.ProdID = P.ProductID
Ok, now we’ll add another derived column to tell us the running total of each sale up to that point in time:
The most important things to understand are:
- Our window function contains an ORDER BY clause to order the results by OrderDate.
- Our window function will go down the list of orders one row at a time when calculating what the ‘Running Total‘ value needs to be for that row.
- The delimiter of
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWis how the running total is calculated.
When SQL Server is on a row, it will look at the Sale Price of that row (which is referred to as “the current row”) plus the sum of all Sales Prices of the rows before it. That’s what ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means. The resulting summation is what is used as the ‘Running Total‘ value for that row.
And actually, the delimiter ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENCT ROW is so common that SQL Server introduced a shorthand way to write it: ROWS UNBOUNDED PRECEDING
The ORDER BY clause guarantees that we’re gathering a running total in the order that the Orders were placed.
Folks, this tutorial is meant to just give you a sniff of how to use window functions in Microsoft SQL Server. If you want to know more, you should start with the beginner-friendly introduction:
SQL Server Window Functions: An introduction for beginners
Or better yet, download this FREE EBook:
FREE SQL Window Functions EBook!
This book discusses all the different window functions available to us in Microsoft SQL Server, including:
- Aggregate window functions, such as SUM, MIN and MAX
- Ranking window functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE
- Offset window functions: FIRST_VALUE, LAST_VALUE, LAG and LEAD
It will definitely be a great resource for you to reference during your career as a database professional. Don’t miss out! Download it today!
Partitioning our running total values:
The last thing I wanted to discuss is how we can introduce partitioning to our result set. What if we wanted to see a running total for each customer?
We can do that easily by introducing a PARTITION BY clause in our window function:
Now, instead of seeing a running total for all customers, we’re seeing a running total for each customer. I have outlined each customer’s running totals in different colored boxes.
Pretty cool stuff!
Next Steps:
Leave a comment if you found this tutorial helpful!
If you want to learn more about window functions and how they can help you become better at querying databases, the best thing you can do is download the FREE Ebook:
FREE SQL Window Functions EBook!
The topic of window functions in SQL Server is so vast that I decided to write an e-book to thoroughly discuss the topic. I’m offering you this book absolutely FREE. It will be a great resource for you to reference during your career as a database professional. Download it today!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
