Calculating a running total in MySQL

sql

window-functions

performance

best-practices

byAnton Shumikhin·Jan 15, 2025

To swiftly compute a running total in MySQL, you can use session variables. Assuming a table sales with id and amount, the following SQL snippet will do the job:

SELECT id, amount, @total := @total + amount AS running_total
FROM sales, (SELECT @total := 0) AS var
ORDER BY id;  --order by id, because order matters, unlike those socks in your drawer

This increments @total by the amount for each row retrieved, hence producing a running total ordered by id.

Understanding different contexts

In complex tables or when user variables are not suitable, several alternate methods exist to resolve this scenario. Let's tackle these situations.

User variables and null values

User variables, such as @total, are susceptible to unpredicted behaviour within a single statement in versions prior to MySQL 8.0.22. Avoid the use of user variables and switch to window functions in these cases. Also, ensure running total calculations start with non-NULL values for accuracy.

Employing window functions

MySQL 8.0 and later versions support window functions that allow you to compute running totals efficiently:

SELECT
  id,
  amount,
  SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales;  --sorry, no free pizza included in sales

The window function computes the running total over a sorted set. This eliminates inaccuracies due to uninitialized variables or session states.

Grouping and ordering data

For data that requires running totals per group, (like per day), the GROUP BY clause is your best friend:

SELECT
  date,
  SUM(daily_sales),
  SUM(SUM(daily_sales)) OVER (ORDER BY date ASC) AS running_total
FROM sales
GROUP BY date  -- grouping: it's like being back in school
ORDER BY date;  --we love dates, especially with free dinner

Derived tables and common table expressions

Derived tables and common table expressions (CTEs) provide greater control over your data:

WITH ordered_sales AS (
  SELECT id, date, amount
  FROM sales
  ORDER BY date, id
)
SELECT
  id,
  date,
  amount,
  SUM(amount) OVER (ORDER BY date, id) AS running_total
FROM ordered_sales;  -- watch the total rise like dough in the oven

Maximizing performance with indexing

Columns used in the ORDER BY clause of a window function or in JOIN conditions should be properly indexed to enhance performance. Remember to review execution plans and consider early filtering with a WHERE clause to reduce load, just like you filter out bad pick-up lines.

Good to know

Outside of SQL

Running totals can be computed in the application code if it offers more flexibility or efficiency. This can be notably beneficial when working with large datasets or caching results.