by
SQL Tutorial › Window Functions › Running Totals & Moving Averages

SQL Running Totals and Moving Averages

AdvancedLesson 48 of 6911 min read7 queries run on MySQL 8.4

A running total adds each row to everything before it: revenue to date, stock level after each movement, headcount over time. A moving average smooths a series over the last few rows. Both are one window function each, once you understand the frame: which rows around the current one are included.

Advertisement
In this lesson
  • Build running totals with SUM() OVER (ORDER BY ...)
  • Restart totals per group with PARTITION BY
  • Control the frame with ROWS BETWEEN
  • Calculate moving averages and percent of total

Daily revenue

All examples use the revenue per order date, built in a CTE:

SQL
WITH daily AS (  SELECT o.order_date, SUM(oi.quantity * p.price) AS revenue  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY o.order_date)SELECT * FROM daily ORDER BY order_date;
ResultMySQL 8.4
order_daterevenue
2025-01-054744
2025-01-178999
2025-02-024298
2025-03-083286
2025-03-225497
2025-04-10799
2025-04-2820996
2025-05-153499
2025-06-011145
9 rows in set

Running total

SQL
WITH daily AS (  SELECT o.order_date, SUM(oi.quantity * p.price) AS revenue  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY o.order_date)SELECT order_date, revenue,       SUM(revenue) OVER (ORDER BY order_date) AS revenue_to_dateFROM dailyORDER BY order_date;
ResultMySQL 8.4
order_daterevenuerevenue_to_date
2025-01-0547444744
2025-01-17899913743
2025-02-02429818041
2025-03-08328621327
2025-03-22549726824
2025-04-1079927623
2025-04-282099648619
2025-05-15349952118
2025-06-01114553263
9 rows in set
How this query works, step by step
  1. The CTE gives one row per day with that day’s revenue.
  2. SUM(revenue) OVER (ORDER BY order_date) adds the revenue of the current day and every earlier day.
  3. The last row’s running total is the grand total of all non-cancelled orders.

Running total per group

Each customer’s cumulative spend, order by order:

SQL
WITH order_totals AS (  SELECT o.customer_id, o.order_id, o.order_date, SUM(oi.quantity * p.price) AS total  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  WHERE o.status <> 'Cancelled'  GROUP BY o.customer_id, o.order_id, o.order_date)SELECT customer_id, order_id, order_date, total,       SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_to_dateFROM order_totalsORDER BY customer_id, order_date;
ResultMySQL 8.4
customer_idorder_idorder_datetotalcustomer_to_date
110012025-01-0547444744
110032025-02-0242989042
110082025-04-282099630038
210022025-01-1789998999
210072025-04-107999798
310092025-05-1534993499
410052025-03-0832863286
510062025-03-2254975497
510102025-06-0111456642
9 rows in set
How this query works, step by step
  1. PARTITION BY customer_id restarts the running total for each customer.
  2. Inside each customer, ORDER BY order_date accumulates order by order.

Window frames: ROWS and RANGE

The frame decides which rows the function sees for the current row:

FrameIncludes
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWfirst row up to the current row
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWthe current row and the two before it
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGprevious, current and next
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGthe whole partition
Note

With ORDER BY and no explicit frame, MySQL uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as one step, so tied rows get the same running total. Write ROWS when you want strictly row-by-row totals.

SQL
SELECT first_name, salary,       SUM(salary) OVER (ORDER BY salary) AS range_total,       SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_totalFROM employeesWHERE dept_id = 1ORDER BY salary, emp_id;
ResultMySQL 8.4
first_namesalaryrange_totalrows_total
Amit250002500025000
Priya7200016900097000
Karan72000169000169000
Rahul98000267000267000
Asha125000392000392000
5 rows in set
How this query works, step by step
  1. Priya and Karan both earn 72,000.
  2. With the default RANGE frame, both tied rows include each other: they both show 169,000.
  3. With ROWS, the first of the pair shows 97,000 and the second 169,000.

Moving average

A 3-order moving average of order values smooths out spikes:

SQL
WITH order_totals AS (  SELECT o.order_id, o.order_date, SUM(oi.quantity * p.price) AS total  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p     ON p.product_id = oi.product_id  GROUP BY o.order_id, o.order_date)SELECT order_id, order_date, total,       ROUND(AVG(total) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)) AS moving_avg_3FROM order_totalsORDER BY order_date;
ResultMySQL 8.4
order_idorder_datetotalmoving_avg_3
10012025-01-0547444744
10022025-01-1789996872
10032025-02-0242986014
10042025-02-141899910765
10052025-03-0832868861
10062025-03-2254979261
10072025-04-107993194
10082025-04-28209969097
10092025-05-1534998431
10102025-06-0111458547
10 rows in set
How this query works, step by step
  1. For each order, the frame holds that order and the two before it.
  2. AVG over the frame is the moving average.
  3. The first two rows have fewer than three orders in the frame, so their average uses one and two orders.

Percent of total and cumulative percent

Which categories make up 80% of revenue (a Pareto analysis)?

MySQL
WITH cat AS (  SELECT p.category, SUM(oi.quantity * p.price) AS revenue  FROM order_items oi  JOIN products p ON p.product_id = oi.product_id  GROUP BY p.category)SELECT category, revenue,       ROUND(100 * revenue / SUM(revenue) OVER (), 1) AS pct,       ROUND(100 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER (), 1) AS cumulative_pctFROM catORDER BY revenue DESC;
ResultMySQL 8.4
categoryrevenuepctcumulative_pct
Furniture5049369.969.9
Electronics1609322.392.1
Stationery56767.9100.0
3 rows in set

Running total without window functions

Before MySQL 8, running totals needed a correlated subquery, which re-adds all earlier rows for every row (slow on big tables):

SQL
SELECT o.order_id, o.order_date,       (SELECT COUNT(*) FROM orders x WHERE x.order_date <= o.order_date) AS orders_to_dateFROM orders oORDER BY o.order_date;
ResultMySQL 8.4
order_idorder_dateorders_to_date
10012025-01-051
10022025-01-172
10032025-02-023
10042025-02-144
10052025-03-085
10062025-03-226
10072025-04-107
10082025-04-288
10092025-05-159
10102025-06-0110
10 rows in set

Exercise: Cumulative headcount

List employees by hire_date with a running count of employees hired so far.

Expected resultMySQL 8.4
first_namehire_dateheadcount
Meera2015-01-051
Vikram2017-11-202
Asha2018-04-123
Sneha2019-02-254
Rahul2019-07-015
Kavya2020-09-096
Priya2021-01-187
Neha2022-03-148
Karan2022-08-299
Arjun2023-06-0510
Rohan2024-01-2211
Amit2025-06-0212
12 rows in set
Hint 1

COUNT(*) OVER (ORDER BY hire_date).

Quick quiz

1. Which window gives a running total?

2. What frame gives a 3-row moving average?

3. Why do two tied rows show the same running total by default?

Running total FAQ

How do I calculate a running total in SQL?

Use SUM(amount) OVER (ORDER BY date_column). Add PARTITION BY to restart the total for each group.

What is the difference between ROWS and RANGE?

ROWS counts physical rows. RANGE works on values, so rows with the same ORDER BY value are treated together.

How do I calculate a moving average in MySQL?

AVG(value) OVER (ORDER BY date ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW) gives an n-row moving average.