Rolling Total in SQL: What It Is and How to Compute It

Calculating a rolling total in SQL is simple when you use window functions. These handy metrics are similar to running totals and moving averages, and they’ll help you build a clear picture of your data. In this article, we'll explain and explore rolling totals.

In SQL, a rolling total is an important metric that helps you see how trends change over time, giving a clear picture of your data. It calculates a sum over a "window" or subset of rows that moves through the result set. For example, to track website visits, you could query the total visits for each day and also sum the previous 7 days to spot trends. When you use SQL’s window functions, you can see these trends in each row of your query results – without resorting to external tools.

To easily compute rolling totals in SQL, you’ll need window functions. Our Window Functions course is perfect if you want to really dig into this topic. When you take this course, you’ll complete 218 exercises that cover the syntax and semantics of window functions. You’ll learn window functions’ full potential and typical use cases. And you’ll gain an understanding of advanced SQL that will enable you to perform more complex and efficient analyses.

A Simple Rolling Total in SQL

Let’s see a common business situation in which the rolling total demonstrates its usefulness. Consider a table called daily_sales that records a store’s daily sales totals:

If we look at this data, or even if we create a chart from it, this is what we see:

We can analyze specific events, such as finding dates with unusually low sales. For example, sales on 2024-08-03 are lower than on other days. However, this way of visualizing the information is not effective for detecting trends in the evolution of sales. For that, we need to look at rolling totals. And for that, we’ll need SQL window functions.

If you need a quick reference guide to SQL window functions, keep this Window Functions Cheat Sheet at hand. It will speed up your daily data analysis tasks.

Computing a Rolling Total in SQL

Let’s write an SQL query that returns the two columns from daily_sales and adds a three-day rolling total to the result set:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (
        	ORDER BY purchase_date
        	ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
     ) AS rolling_total
FROM daily_sales;

In the above example, the OVER clause after the SUM() function defines the window (i.e. the group of rows) that we want to sum for each row. In this case, it tells SQL that the sum should include data from the two preceding rows and the current one. The order is based on the purchase date. This is our rolling window: the current day and two days prior.

The query yields this data:

In the first row, notice that the rolling total just repeats the value from the total_sales column – there are no previous rows to sum. In the second row, it sums the total_sales value for the first and second rows; from the third row on, it returns the sum of sales values from the two preceding rows and the current one.

Let’s make a chart with the rolling total data to understand how it helps us analyze trends:

The first thing that jumps out when we look at this chart is that we should discard the first two data points (the first two dates), because they do not represent a three-day total like the rest of the dates. From 2024-08-03 onwards, the line becomes almost horizontal, indicating that the three-day sales totals remain stable. This is true until the last date, which shows a slight uptick because the total is no longer affected by the “drop” in sales from 2024-08-03.

With such a small data set, even minor fluctuations affect the overall trend; with a large data set, this would not be the case. However, we can conclude that the combination of aggregate functions and window functions “smoothes out” the effect of fluctuations or anomalies in the data. This allows for clear visualization of data trends.

Rolling Total, Running Total, and Moving Average

Besides the  rolling total, there are two other common use cases for window functions: the running total and the moving average.

• A running total is a cumulative calculation that runs through a result set. For each row, it calculates the sum of all the values in a column, from the first row to the current row. It is similar to the rolling total, but it always sums starting from the first row of the result set.
• A moving average is like a rolling total in that it takes values from the window defined in relation to the current row. The difference is that it returns the average of these values instead of the total.

Calculating these metrics is quite similar to calculating a rolling total. Let’s look at a couple of examples.

Computing a Running Total in SQL

Here’s an example of a running total:

SELECT
    purchase_date,
    total_sales,
    SUM(total_sales) OVER (ORDER BY purchase_date) AS running_total
FROM
    daily_sales;

The difference between running total and rolling total in SQL is the definition of the window in the OVER clause. Instead of defining a subset that spans a number of rows relative to the current row, we just need to specify the order criterion; it’s understood that the total covers from the first row to the current one. You can find more information about running totals in our article What Is a Running Total and How Do You Compute It in SQL.

Computing a Moving Average in SQL

The concept of moving average is very similar to that of s rolling total. The aggregate function AVG() operates over a window that scrolls over a defined number of rows. In fact, the query to create a moving average in SQL is practically the same as the one we used for the rolling total. We just need to change the SUM() function to AVG():

SELECT
    purchase_date,
    total_sales,
    AVG(total_sales) OVER (
        	ORDER BY purchase_date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_average
FROM
    daily_sales;

This will return the average sales over a three-day period: the current day and the two preceding days. You can find more specifics about moving averages in this article.

Other Use Cases for Rolling Totals

Weekly Energy Consumption

There are many other scenarios where a rolling total can be applied. Most examples involve chronological order tables, where the data window represents a time interval ( e.g. 7 days, 30 days, 3 months, etc.).

One example could be to analyze the evolution of a household’s energy consumption over time. Such information could be stored in a table (called daily_consumption_per_day) that records the total kilowatt hours consumed by the household each day.

Here’s the query:

SELECT
    measure_date,
    daily_consumption,
    SUM(daily_consumption) OVER (
        ORDER BY measure_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS weekly_consumption
FROM
    daily_consumption_per_day;

For each date, the result shows the sum of the consumption for the last 7 days (including the current day). This allows us to see the variation of trends in energy consumption over time.

Window functions have a lot of potential. Learn everything that you can do with them in our comprehensive guide to SQL window functions.

Using Rolling Totals Beyond Time Series Analysis

Most of rolling totals’ use cases are related to time series analysis. However, these stats can be applied to any data set sorted by a dimension where it makes sense to make aggregate calculations (e.g. totals, averages) over sliding windows that isolate subsets of that dimension.

An example would be an analysis of the cost of medical benefits by age. Suppose we have a table that records the cost of medical benefits received by people of various ages. If we order the rows by age, a rolling total will allow us to see how medical costs vary by age range.

Let's assume that the table is called medical_costs_by_age and that it has two columns: age and total_medical_cost. To analyze this information, we will write a query with a rolling total that spans a five-year window centered on the age of the current row (from two years below to two years above):

SELECT
    age,
    total_medical_cost,
    SUM(total_medical_cost) OVER (
        ORDER BY age
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
        ) AS weighted_medial_cost
FROM
    medical_costs_by_age;

From this example, we can get a clear and quite granular understanding of the relationship between age groups and healthcare costs.

Want to Learn More About Rolling Totals in SQL?

Throughout this article, we have seen several ways to use a rolling total in SQL. We’ve compared it with similar metrics, most notably the running total and the moving average.

If you didn't know about SQL window functions before reading this article, you are probably impressed with what they can do. If you want to learn about window functions from scratch, I recommend our Window Functions course. If you’re already familiar with them, take the Window Functions Practice Set. It has 100 interactive window function exercises that use three different real-life databases. And our learning platform provides you with all the necessary tools – you don’t need to install anything. Just use your browser and an internet connection and you’re good to go!

Finally, you can keep your grasp of rolling totals in SQL sharp with this set of free window function practice exercises. And, if you aspire to a job that requires SQL mastery and want to prepare for the job interview, don’t forget to read about these SQL window function interview questions. Thanks for reading this article, and happy learning!