SQL Running Totals and Moving Averages

Finance wants to see Beacon Robotics' total salary commitment grow as each employee was hired, a running total, in hire order, not a single final number. SUM() as a window function, with ORDER BY, does exactly that.

Learning Objectives

Build a running total using SUM() OVER (ORDER BY ...).
Build a moving average over a fixed number of preceding rows, using an explicit ROWS BETWEEN frame.
Explain why SUM()'s default frame, unlike LAST_VALUE()'s, is exactly correct for a running total.

Ordered Rows

hire_date order

SUM() / AVG() OVER (ORDER BY ...)

Frame grows with each row

Running Total / Moving Average

Computed as of each row

Trend Over Time

Visible row by row, not just a final total

A Running Total of Salary, by Hire Date

Unlike the previous lesson's LAST_VALUE(), this works correctly with no explicit frame at all. The default frame (UNBOUNDED PRECEDING to CURRENT ROW, whenever ORDER BY is present) is precisely what a running total needs, sum everything up to and including this row, nothing more, nothing less.

MySQL / MariaDB / PostgreSQL / SQL Server / Oracle / SQLite

SELECT first_name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS running_salary_total
FROM employees;

A 3-Hire Moving Average of Salary

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW explicitly narrows the frame to exactly 3 rows (the current one plus the 2 before it), instead of growing without bound the way the running total's default frame does. The first two rows in the whole result average over however many rows are actually available (1, then 2), there's no error, just a smaller frame at the very start.

MySQL / MariaDB / PostgreSQL / SQL Server / Oracle / SQLite

SELECT first_name, hire_date, salary,
       AVG(salary) OVER (
         ORDER BY hire_date
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_salary_3
FROM employees;

Try It Yourself

Write a query showing a running count of employees hired, in hire_date order, on Oracle.

Answer

SELECT first_name, hire_date,
       COUNT(*) OVER (ORDER BY hire_date) AS running_hire_count
FROM employees;

Frequently Asked Questions

Why does SUM() with ORDER BY give a correct running total by default, when LAST_VALUE() didn't?

The default frame (UNBOUNDED PRECEDING to CURRENT ROW) is exactly the definition of a running total, sum everything from the start through the current row. LAST_VALUE()'s problem was that it reads the frame's last row, which that same default frame pins to the current row, not the partition's true end, a running total never needed the frame's end to reach beyond the current row in the first place.

Can a moving average's frame be based on a time range instead of a fixed row count?

Yes, using RANGE instead of ROWS in the frame clause, though RANGE-based framing with precise time intervals has more dialect-specific variation across these six databases than the simpler ROWS BETWEEN N PRECEDING form used here, which is why this lesson sticks to ROWS.

Common Beginner Mistakes

Expecting a moving average's frame to always contain exactly N rows

Only once there are at least N rows available, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW on the very first row of the result only has that 1 row to average, there's no error, the frame is just smaller than requested at the start.

Adding PARTITION BY and forgetting it resets the running total per group

SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) restarts the running total at 0 for each new department, which is usually exactly what's wanted for a per-department running total, but is easy to forget when a single company-wide running total was actually intended.

Best Practices

Rely on the default frame for a running total, it's already correct, no explicit ROWS BETWEEN needed.
Always specify ROWS BETWEEN N PRECEDING AND CURRENT ROW explicitly for a moving average, the default frame would otherwise keep growing instead of staying a fixed size.
Add PARTITION BY deliberately, and only when a running total or moving average should actually restart per group.

Interview Questions

Write a query producing a running total of salary in hire order, and explain why no explicit frame is needed.

SELECT first_name, hire_date, salary, SUM(salary) OVER (ORDER BY hire_date) AS running_total FROM employees; — no explicit frame is needed because the default frame, whenever ORDER BY is present, already runs from the partition's start through the current row, which is exactly the definition of a running total.

Write a query producing a 7-day-equivalent, 7-row moving average of salary.

SELECT first_name, hire_date, salary, AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7 FROM employees; — ROWS BETWEEN 6 PRECEDING AND CURRENT ROW covers exactly 7 rows total (6 before plus the current one), with the window sliding forward one row at a time.

Summary

SUM() and AVG() become running totals and moving averages as window functions. A running total needs no explicit frame, the default already matches its definition. A moving average needs an explicit ROWS BETWEEN N PRECEDING AND CURRENT ROW to keep the window a fixed size instead of growing without bound.

What's Next?

SQL Top-N-Per-Group is next, the capstone for this module: combining ROW_NUMBER() with a subquery to answer one of the most common real interview questions in SQL.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.

Or try 50 SQL practice questions →