Aggregate Window Functions (SUM, AVG, COUNT OVER) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Window Functions Introduction (OVER, PARTITION BY), Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
Builds toward Window Frames (ROWS, RANGE, GROUPS), Date Spine Construction and Zero-Fill Patterns, Multi-CTE Query Architecture, NULL Propagation in Complex Queries
What are Aggregate Window Functions in SQL?
SUM, AVG, and COUNT used with OVER give you two different behaviors depending on whether you include ORDER BY inside OVER.
Without ORDER BY inside OVER, the aggregate computes a single value for the partition and places that same value on every row. With ORDER BY inside OVER, the aggregate accumulates: each row gets the sum (or average, or count) of all rows up to and including its position in the ordering. These are two different analytical questions, and the presence or absence of ORDER BY is what separates them.
How do you compute a running total in SQL?
You're tracking order revenue over time. You want each order to show both the overall total and the running total as of that date:
SELECT ordered_at::date AS order_date, total_amount, SUM(total_amount) OVER () AS grand_total, SUM(total_amount) OVER (ORDER BY ordered_at) AS running_total FROM orders ORDER BY ordered_at
grand_total is the same on every row — the sum of all orders. running_total starts small and grows with each row. On the last row, it equals grand_total.
How do you restart a running total per group?
The same logic applies to other aggregates. Add PARTITION BY to reset within groups:
SUM(total_amount) OVER (PARTITION BY status ORDER BY ordered_at) -- running total per status
AVG(total_amount) OVER (PARTITION BY status ORDER BY ordered_at) -- running average per status
COUNT(*) OVER (PARTITION BY status ORDER BY ordered_at) -- running count per statusEach accumulates within its partition, restarting at the start of each new group.
COUNT(*) counts rows regardless of NULL. COUNT(column) counts only non-NULL values. SUM, AVG, MIN, and MAX all skip NULL values, consistent with how they behave as regular aggregates.
Why does my running total jump ahead on tied timestamps?
The one thing that trips people up: when multiple rows share the same ORDER BY value, the running total jumps ahead.
PostgreSQL's default behavior for ordered windows groups rows with the same sort value together and includes all of them in each other's running total. If three orders all have the same ordered_at timestamp, each of those three rows shows a running total that already includes all three of their amounts — not a strict row-by-row accumulation.
What is the difference between ROWS and RANGE in a window frame?
This is called RANGE mode. If you want strict row-by-row accumulation regardless of equal sort values, add ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:
SUM(total_amount) OVER (
ORDER BY ordered_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)In ROWS mode, each row accumulates only up to its own physical position. For time-series work where multiple events share a timestamp, this usually produces the result you expect.
You write SUM(revenue) OVER (ORDER BY order_date). Two rows share the same order_date. What does each row's running total include?
Practice Aggregate Window Functions in SQL
Brightlane's finance team computes the cumulative revenue total as orders are processed in sequence by ID.
Write a query to return the ID and amount of every order, plus the running total of total_amount accumulated from the first order through that order in order of id.
Assumptions:
- The
orderstable has one row per order with anidand atotal_amount. - Orders are processed in ascending
idorder. The running total at each row is the combinedtotal_amountacross every order whoseidis less than or equal to that row'sid.
Output:
- One row per order, with columns
id,total_amount, andrunning_total.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solutionStation Zero, our free browser SQL game, teaches this concept inside a story. No signup.
run a running total in Station Zero9 Aggregate Window Functions practice problems
Write a query to return the ID and amount of every order, plus the running total of total_amount accumulated from the first order through that order in order of id.
Write a query to return the ID and status of every order, plus the running count of orders from the first record through that order in order of id.
Write a query to return the ID, name, and price of every product, plus the running average price accumulated from the first product through that product in order of id.
Write a query to return the ID, status, and amount of every order, plus the running total of total_amount within the order's status, ordered by id.
Write a query to return the ID, customer ID, and total amount of every order, plus the running count of orders placed by that customer, accumulated in order of id.
Write a query to return the ID and amount of every order, plus the grand total of total_amount across every order and the running total accumulated through that order in order of id.
Write a query to return the ID, name, and price of every product, plus the running minimum and running maximum price observed from the first product through that product in order of id.
Write a query to return the ID, name, and price of every product, plus the running sum of price ordered by price ascending. Sort the final result by price ascending.
Write a query to return the ID and amount of every order, plus a running total of total_amount that adds exactly one order's amount per row, ordered by id.
Start learning to practice all 9 Aggregate Window Functions problems, with instant grading and mastery tracking.
Deeper guides on Aggregate Window Functions
- what attaching OVER to an aggregate changes
It is a clause, not a function, and the difference explains the error you are getting.
Common questions about Aggregate Window Functions
Does a running total include the current row?
Yes. Each row shows the total of everything up to and including itself, so the first row equals its own value and the last equals the grand total. That is what makes the final row a useful check on the calculation.
Can you compute a running count as well as a running sum?
Yes. Any aggregate takes an OVER clause, so a count, an average or a minimum can all accumulate the same way. The ORDER BY inside OVER is what turns a whole-partition figure into an accumulating one.
Why is my running total the same on every row?
Because the OVER clause has no ORDER BY. Without one the aggregate covers the whole partition and every row gets the same grand total. Adding an ORDER BY inside the parentheses is what makes it accumulate.
