Problem
A friend asked me why their running total query wasn’t working as expected. They noticed the values kept repeating for each row. After reviewing the query, I found that the issue was due to a non-unique ORDER BY clause in the window function. Let’s see how we can fix this.
Solution
The ORDER BY clause needs to be deterministic for a running total to work the way you expect. Sometimes, though, it’s tough to define a unique set of columns that makes the running total behave correctly. In this article, I’ll show you how to get better results with your running totals and share a few performance tips for window functions.
Running Totals Explained
Any data analyst or report builder has probably seen and even created a running total. When you need to calculate the sum of transactions from a list, a running total is your friend. It adds the current value to all the previous ones. For example, if you want to calculate sales for a specific month and view daily summaries while also seeing the cumulative total up to each day, that’s when a running total comes in handy.
To illustrate the concept of a running total, I created one in Excel so we’re all on the same page.
Build Our Demo Environment
We’ll set up a demo environment for the remainder of this article, consisting of one table that tracks the daily consumption of pizza by four childhood heroes of mine.
Run the code below to follow along.
/* MSSQLTIPS.com */
DROP TABLE IF EXISTS dbo.PizzaTracker;
GO
CREATE TABLE dbo.PizzaTracker
(
Id INT IDENTITY(1, 1),
TurtleName VARCHAR(20) NOT NULL,
SlicesEaten INT NOT NULL,
DateEaten DATE NOT NULL,
CONSTRAINT PK_PizzaTracker_Id
PRIMARY KEY CLUSTERED (Id)
);
GO
INSERT INTO dbo.PizzaTracker
(
TurtleName,
SlicesEaten,
DateEaten
)
VALUES
('Leonardo', 3, '2025-10-01'),
('Michelangelo', 5, '2025-10-01'),
('Donatello', 4, '2025-10-01'),
('Raphael', 6, '2025-10-01'),
('Michelangelo', 4, '2025-10-02'),
('Michelangelo', 3, '2025-10-02'),
('Leonardo', 2, '2025-10-02'),
('Leonardo', 1, '2025-10-02'),
('Donatello', 5, '2025-10-03'),
('Raphael', 2, '2025-10-03');
GOUsing T-SQL to Build a Running Total
To build a running total in any modern version of SQL, we’ll use a window function. You can turn almost any aggregate function into a window function by adding an OVER clause. Inside the OVER clause, you add a couple of additional clauses, the PARTITION BY and, for our purposes, the ORDER BY, when it comes to a running total.
/* MSSQLTips.com */
SELECT TurtleName,
DateEaten,
SlicesEaten,
SUM(SlicesEaten) OVER (PARTITION BY TurtleName ORDER BY DateEaten) AS RunningTotal
FROM dbo.PizzaTracker
ORDER BY TurtleName,
DateEaten,
Id;
GOYou can see that we are using a SUM and setting the ORDER BY clause to DateEaten, and I’m partitioning by the TurtleName column. Let’s go ahead and run this query and talk about the results.
Notice that some of the values in the RunningTotal column are repeated. Why is that? It comes down to the ORDER BY clause.
ORDER BY and Determinism
A term you might be familiar with is determinism. It basically means that you can rely on receiving repeatable results if you enter the same value. You mainly hear about determinism when it comes to functions. For example, a SUM function is deterministic because it will always return the same results as long as you enter the same value. A function that is not deterministic is GETDATE because when you run it, you will get vastly different results from one execution to the next.
Determinism also comes into play when you are talking about ORDER BY clauses.
Framing the Window
You’ve likely seen a window function used before, but one additional thing you can add to it is the frame. A frame is an extra element you can put on a window to define which rows you want to look at. By default, SQL uses a frame known as RANGE, which groups together rows with the same ORDER BY value rather than focusing on their row position. I’ve listed the default frame below.
/* MSSQLTips.com */
SELECT TurtleName,
DateEaten,
SlicesEaten,
SUM(SlicesEaten) OVER (PARTITION BY TurtleName
ORDER BY DateEaten
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal
FROM dbo.PizzaTracker
ORDER BY TurtleName,
DateEaten,
Id;
GOWhen we add the ORDER BY clause, the results are not unique. RANGE treats both rows as the same when we have the same date, and that’s why the running total value is repeated. One way to overcome this is to use ROWS instead of RANGE in the framing. However, we are still not fixing the root problem.
/* MSSQLTips.com */
SELECT TurtleName,
DateEaten,
SlicesEaten,
SUM(SlicesEaten) OVER (PARTITION BY TurtleName
ORDER BY DateEaten
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal
FROM dbo.PizzaTracker
ORDER BY TurtleName,
DateEaten,
Id;
GOI think a better way is to add a more unique value. In the example below, I’m adding the ID to the ORDER BY, thereby making the ORDER BY unique.
/* MSSQLTips.com */
SELECT TurtleName,
DateEaten,
SlicesEaten,
SUM(SlicesEaten) OVER (PARTITION BY TurtleName
ORDER BY DateEaten, Id
) AS RunningTotal
FROM dbo.PizzaTracker
ORDER BY TurtleName,
DateEaten,
Id;
GO
In an ideal world, you would track another, more detailed value, like the meal of the day, to know how many pieces of pizza were eaten at each meal.
Aggregate Before the Window
I think an even better way to make the data more usable is to aggregate it before applying the window function. Since we don’t have a more granular way to define the data, let’s make it unique by first grouping the days together.
/* MSSQLTips.com */
WITH DailyTotals
AS (SELECT TurtleName,
DateEaten,
SUM(SlicesEaten) AS DailySlices
FROM dbo.PizzaTracker
GROUP BY TurtleName,
DateEaten
)
SELECT TurtleName,
DateEaten,
DailySlices,
SUM(DailySlices) OVER (PARTITION BY TurtleName
ORDER BY DateEaten
) AS RunningTotal
FROM DailyTotals
ORDER BY TurtleName,
DateEaten;
GO
Now, this suggestion might not work for you, but at least try it if you don’t need to see the details of each row.
Optimizing Performance
Whenever we write a query, we want it to perform well. However, if you’re only running this query once, creating an index for it isn’t worth it. But if you plan to run the query multiple times and performance matters, I strongly suggest adding a supporting index.
Itzik Ben-Gan introduced the Partition, Order, and Covering (POC) index design pattern for window functions. For the key, you add the PARTITION BY as the leading key, followed by the ORDER BY column. Finally, you include any other columns needed for the covering portion. To create an index for our query, it would resemble the example below.
/* MSSQLTips.com */
DROP INDEX IF EXISTS IX_PizzaTracker_RunningTotal ON dbo.PizzaTracker;
GO
CREATE NONCLUSTERED INDEX IX_PizzaTracker_RunningTotal
ON dbo.PizzaTracker (TurtleName, DateEaten, Id)
INCLUDE (SlicesEaten);
GOI’ve added a query that uses my new index, followed by the same query forced to use the clustered index defined on the table.
/* MSSQLTips.com */
SELECT TurtleName,
DateEaten,
SlicesEaten,
SUM(SlicesEaten) OVER (PARTITION BY TurtleName ORDER BY DateEaten, Id) AS RunningTotal
FROM dbo.PizzaTracker WITH (INDEX(PK_PizzaTracker_Id))
ORDER BY TurtleName,
DateEaten,
Id;
GO
SELECT TurtleName,
DateEaten,
SlicesEaten,
SUM(SlicesEaten) OVER (PARTITION BY TurtleName ORDER BY DateEaten, Id) AS RunningTotal
FROM dbo.PizzaTracker WITH (INDEX(IX_PizzaTracker_RunningTotal))
ORDER BY TurtleName,
DateEaten,
Id;
GO
The screenshot shows that the primary cost of the clustered index query is attributed to the sort operator.
I want to emphasize again that if you’re only running this query once and not dealing with billions of rows, it’s not worth adding a new index that requires maintenance. Indexes are great, but they come with a performance cost. There’s no such thing as a free lunch.
Older Method
Early in my career, I worked with SQL Server 7.0 and 2000, and I remember not having access to all the functions available today. Window functions were first introduced in SQL Server 2005, and aggregate support for them didn’t arrive until 2012. So how would we have done something like this before then? The answer would have involved a correlated subquery. I’ve included an example below of what this might have looked like before 2005.
/* MSSQLTips.com */
SELECT p1.TurtleName,
p1.DateEaten,
p1.SlicesEaten,
(
SELECT SUM(p2.SlicesEaten)
FROM dbo.PizzaTracker AS p2
WHERE p2.TurtleName = p1.TurtleName
AND (
p2.DateEaten < p1.DateEaten
OR (
p2.DateEaten = p1.DateEaten
AND p2.Id <= p1.Id
)
)
) AS RunningTotal
FROM dbo.PizzaTracker AS p1
ORDER BY p1.TurtleName,
p1.DateEaten,
p1.Id;
GOThe query above isn’t terribly ugly, but it doesn’t scale or perform well. I’m thankful that we no longer need to do that.
Comparing to Python
People have asked me whether using Pandas would perform better than a window function in SQL. I never know how to answer because it’s like asking if a car is faster than a boat; it depends on where you’re traveling. If your data already lives in SQL Server or an Azure variant, I wouldn’t recommend using Python unless you have a specific reason. Maybe it’s part of an ML notebook, or you need to apply some complex business logic to the window frame. But at face value, no, I don’t think Python will be faster than SQL for calculating a running total.
Key Points
- If you want your running total to actually appear as a running total, make sure you are adding a unique combination of columns in the ORDER BY clause of the window. You can also achieve this by using the ROWS frame.
- When performance matters, consider including the POC index as shown by Itzik Ben-Gan. However, if this is a one-time query, unless you really want to add the index, just skip it.
- It might be best to aggregate days first before applying the window function. In the example, I summed days to get the total Pizza eaten before applying a window. If that doesn’t work, consider creating a unique identifier or making the data more granular. Without the data, you can’t truly report on it.
Next Steps
- Something similar you can do with a window function is calculating a moving average. I wrote the article “Calculate a Moving Average with T-SQL Windowing Functions” to explore this topic further.
- Burt King provides several examples of using partitioning in his article, “SQL Partition By Examples for Aggregating Data”. If you love window functions as much as I do, give it a read.
- If you’re interested in learning more about framing, check out Kathi Kellenberger’s article, “What is the Difference between ROWS and RANGE?”
