A daily sales report may show both today's sales and all sales so far. A second column can smooth the daily ups and downs by averaging the latest three observations. Those columns answer different questions: the running total accumulates history, while the moving average summarizes a changing neighborhood.
SQL window aggregates make both calculations possible without discarding each day's row. SUM becomes a running total when its frame starts at the beginning and ends at the current row. AVG becomes a moving average when its frame covers only a limited number of rows. Naming the frame explicitly makes the calculation easier to review, especially when several observations share the same date.
Choose the unit of observation
Before writing SQL, decide what one row represents. If a row is a purchase, a three-row average means three purchases. If a row is a daily total, it means three recorded days. Those reports can produce very different numbers even though the window syntax looks identical.
For a daily report, aggregate purchases by date first. If there are several stores, group by store and date, then partition the window by store. This gives each store its own running total. Combining all stores first instead answers a company-wide question, which may also be useful but should have a different label.
Always establish a stable order. A date uniquely identifies a row in the example below. For transaction-level accumulation, use a timestamp and unique transaction ID. A final query order controls the visible report; the order inside the window controls which observations contribute to each calculation.
Read the frame in plain language
UNBOUNDED PRECEDING means the first row of the partition. CURRENT ROW means the row currently receiving its calculated value. Together they make the running total include every preceding observation and the current observation.
2 PRECEDING starts two rows before the current one. With the current row included, that is a three-row average. On the first row, only one observation exists. On the second row, only two exist. SQL uses the available rows rather than inventing missing earlier values.
If the business wants averages only after three observations exist, calculate a count over the same frame and display the average only when the count is 3. Count the amount column when NULL values should reduce the number of measured observations; count all rows when the rule concerns the number of records regardless of missing amounts.
For yearly accumulation, partition by the relevant year and other entity columns. For a lifetime accumulation, do not reset by year. The partition defines where history restarts, so it must agree with the name shown on the dashboard.
Missing days and tied dates
A three-row frame is not automatically a three-calendar-day frame. If October 2 is absent, the next three recorded rows span more than three days. To produce a daily time series, join a calendar table and choose whether absent sales mean zero activity or incomplete data. Leave unknown values distinguishable from genuine zero sales.
When multiple rows share an ordering date, a default frame can include peer rows together. That may make the total jump by several purchases at once. An explicit ROWS frame plus a unique tie-breaker gives step-by-step accumulation. A date-level aggregate can instead intentionally combine the day's purchases before the window runs.
Do not use a moving average as evidence that data has become more accurate. It smooths variation and can conceal a sudden change. Show the raw amount alongside the average when the reader needs to spot individual spikes or missing observations.
The SQLite window documentation explains frame boundaries and peers. Compare supported frame syntax in your engine before converting a row-based window into an interval-based time window.
Practice: reset totals for each store
Create a fresh table with store ID, day label and amount. Give store A amounts 10 and 20, and store B amounts 100 and 50. Calculate each store's running total using PARTITION BY store_id ORDER BY day_label and the same cumulative frame.
The expected totals are 10 then 30 for A, and 100 then 150 for B. If B's first total includes A's sales, the partition is missing or incorrect. If rows appear mixed in the display, add ORDER BY store_id, day_label to the final query; changing only the display order will not repair a missing partition.
Next remove one recorded day and explain whether your moving average still means the last three observations or the last three calendar days. A good solution states the intended period and the missing-data policy before presenting a number. That explanation is part of delivering a trustworthy report.
SQL Running Totals and Moving Averages Examples
Each example shows the code, the output it produces and what each part does.
Four days of sales
Use this standalone dataset in PostgreSQL, MySQL 8+, SQL Server or a modern SQLite database with window support. The day labels sort in chronological order.
Client formatting and numeric precision may differ. The final average is mathematically (20 + 30 + 60) / 3; multiplying the input by 1.0 avoids an integer-only average in engines where integer input affects the result type.
CREATE TABLE daily_sales (
day_label VARCHAR(10) PRIMARY KEY,
amount INTEGER NOT NULL
);
INSERT INTO daily_sales VALUES
('2026-10-01', 10), ('2026-10-02', 20),
('2026-10-03', 30), ('2026-10-04', 60);
SELECT day_label, amount,
SUM(amount) OVER (
ORDER BY day_label
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
AVG(1.0 * amount) OVER (
ORDER BY day_label
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_average
FROM daily_sales
ORDER BY day_label;day_label | amount | running_total | moving_average
2026-10-01 | 10 | 10 | 10
2026-10-02 | 20 | 30 | 15
2026-10-03 | 30 | 60 | 20
2026-10-04 | 60 | 120 | 36.666...SQL Running Totals and Moving Averages FAQ
Can I use GROUP BY instead?
Not for a running total. Grouping produces one summary row per group, such as one total per store. A window aggregate retains each observation and adds the cumulative calculation beside it, so every day keeps its own row.
Why is the first average based on one row?
Its frame cannot reach earlier rows that do not exist. Add a completeness test if the report requires a full frame.
Are NULL amounts counted as zero?
Ordinary SUM and AVG ignore NULL input values. Convert them to zero only when the business meaning supports that decision.
How do I reset a running total for each month or store?
Add PARTITION BY to the window: SUM(amount) OVER (PARTITION BY store_id ORDER BY day) restarts at each store. For monthly resets, partition by the month expression, such as strftime('%Y-%m', day) in SQLite or DATE_TRUNC('month', day) in PostgreSQL.
What is the difference between ROWS and RANGE in a moving average?
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW uses exactly the last seven rows, whatever their dates. A RANGE frame with an interval, such as RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW in PostgreSQL, uses the last seven calendar days, which handles missing days correctly but isn't supported everywhere.
SQL Running Totals and Moving Averages Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Which frame calculates a three-observation moving average including the current row?
SQL Running Totals and Moving Averages Quiz
2 questions to check what you learned.
For amounts 10, 20 and 30, what is the running total on the third row?
- 30
- 20
- 60
Why can a three-row moving average cover more than three calendar days?
- Some calendar days may have no rows in the input
- AVG always skips weekends
- ROWS automatically expands to a whole month