Calculate a Running Total in SQL using a Windows Function

Problem

How do you create a running total on a column using SQL?

Background

There is no built-in function to create a running total, but you can use a windows function to do so. In this case well construction a running total of Weekly Product Sales.

For this solution we’ll work with the WinCode WeeklySales table to calculate a running total. Here is what the data look like.

Note: The tables and data for this example are found located within the EssentialSQL\WinCode GitHub Repo

Solution

Using a running total in SQL can be achieved using window functions. Window functions are used to perform calculations over a group of rows that are related to the current row. The window function is applied to a subset of rows called a window. The window function is then calculated based on the values within the window.

In this example, we will be using the WeeklySales table to calculate the running total for each product. We will be using the SUM() function as the window function. The window frame will be defined using the ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW syntax. This window frame includes all the rows from the start of the partition to the current row.

The SQL code to calculate the running total for each product is as follows:

select SalesDate, Product, Amount,  
   SUM(Amount) over (partition by Product order by SalesDate
            rows between unbounded preceding and current row) as RunningTotal 
from WeeklySales 

Discussion

The above SQL code creates a result set that includes the SalesDate, Product, Amount, and RunningTotal columns.

The RunningTotal column is calculated using the SUM() window function. The window function is applied to each row in the partition that includes the same Product value.

The window frame is defined using the ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW syntax. This window frame includes all the rows from the start of the partition to the current row. The window is ordered by the SalesDate column.

The output of the above SQL code will show the SalesDate, Product, Amount, and RunningTotal columns for each row in the WeeklySales table. The RunningTotal column will show the running total for each product up to the current row.

Using window functions to calculate a running total in SQL is better than other SQL methods because it is efficient, concise, and easy to understand. It is also flexible, as the window frame can be defined to include any subset of rows. Window functions can be used to calculate a variety of calculations, such as running totals, rolling averages, and ranking. They are a powerful tool for data analysis and are a standard feature in most modern SQL databases.

Learn More: Windows Functions in SQL – What Are They?

Window Function Alternatives

If you can’t use window functions to calculate a running total in SQL, you can still achieve the same result using other SQL methods such as subqueries, correlated subqueries, or self-joins. However, these methods can be less efficient and harder to read than using window functions.

Here is an example of how to calculate the running total for each product using a subquery:

SELECT t1.SalesDate, t1.Product, t1.Amount, 
(
   SELECT SUM(t2.Amount)
   FROM WeeklySales t2
   WHERE t2.Product = t1.Product AND t2.SalesDate <= t1.SalesDate
) AS RunningTotal 
FROM WeeklySales t1 

The above SQL code creates a result set that includes the SalesDate, Product, Amount, and RunningTotal columns. The RunningTotal column is calculated using a subquery. The subquery calculates the sum of the Amount column for all rows with the same Product value and a SalesDate less than or equal to the SalesDate of the current row.

The output of the above SQL code will show the SalesDate, Product, Amount, and RunningTotal columns for each row in the WeeklySales table. The RunningTotal column will show the running total for each product up to the current row.

Conclusion

While subqueries can be used to calculate running totals in SQL, they can be less efficient than using window functions, especially when dealing with large datasets. Subqueries require the database to execute multiple queries, which can increase the time it takes to process the data. In addition, subqueries can be harder to read and maintain than using window functions. Window functions provide a cleaner and more efficient way to calculate running totals in SQL, especially for complex queries.