Window Functions Introduction (OVER, PARTITION BY) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this GROUP BY, ORDER BY and Result Sorting
Builds toward ROW_NUMBER, RANK, DENSE_RANK, Aggregate Window Functions (SUM, AVG, COUNT OVER), LAG and LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
What are Window Functions in SQL?
A window function gives you aggregate-style calculations — SUM, AVG, COUNT — while keeping every row in the output.
With GROUP BY, SQL collapses rows into one per group. You get the total, but the individual rows disappear. Sometimes that's what you want. But sometimes you need both: the aggregate and the individual rows together. A report that shows each order alongside the customer's total spend. A product list that shows each item next to the average price in its category. For these questions, GROUP BY collapses too much. The OVER clause is the answer.
What does OVER () do in SQL?
OVER () with nothing inside it runs the calculation across every row in the result set. Every row gets the same computed value:
SELECT id AS order_id, total_amount, SUM(total_amount) OVER () AS grand_total FROM orders LIMIT 10
Every row shows its own total_amount alongside the same grand_total — the sum of all orders. No rows are removed. No groups are collapsed.
What does PARTITION BY do in a window function?
PARTITION BY inside OVER splits the calculation into groups, one per distinct value of the partition column. The function computes independently within each group, but every row still appears:
SELECT
id AS order_id,
status,
total_amount,
SUM(total_amount) OVER (PARTITION BY status) AS status_total
FROM ordersA completed order shows the total for all completed orders in status_total. A pending order shows the total for all pending orders. The partition determines which rows contribute to the calculation for each row.
What does ORDER BY inside OVER change?
ORDER BY inside OVER is different from ORDER BY at the end of a query. At the end, ORDER BY sorts the final result. Inside OVER, it changes what the function computes: an aggregate with ORDER BY inside OVER becomes a running total — the sum of all rows from the start of the partition up to and including the current row:
SELECT
id AS order_id,
ordered_at,
total_amount,
SUM(total_amount) OVER (ORDER BY ordered_at) AS running_total
FROM ordersEach row's running_total is the cumulative sum of all orders up to that date. The ORDER BY inside OVER is not sorting the output — it's defining how the window function accumulates.
Why can you not use a window function in WHERE?
The one thing that trips people up: you cannot filter on a window function result in WHERE.
Window functions are evaluated after WHERE. So WHERE running_total > 1000 doesn't work — SQL hasn't computed the window yet at that point. If you need to filter on a window result, wrap the query in a subquery or CTE and apply the filter outside:
WITH order_running AS (
SELECT id, total_amount,
SUM(total_amount) OVER (ORDER BY ordered_at) AS running_total
FROM orders
)
SELECT *
FROM order_running
WHERE running_total > 1000What is the difference between SUM(revenue) with GROUP BY region and SUM(revenue) OVER (PARTITION BY region)?
Practice Window Functions in SQL
Brightlane's finance team wants every order's amount displayed alongside the company-wide revenue total for at-a-glance comparison.
Write a query to return the ID and amount of every order, plus the combined total amount across every order on each row.
Assumptions:
- The
orderstable has one row per order with anidand atotal_amount. - The grand total is the combined
total_amountacross every order in the table. The same value should appear on every output row.
Output:
- One row per order, with columns
id,total_amount, andgrand_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.
rank the escape routes in Station Zero9 Window Functions practice problems
Write a query to return the ID and amount of every order, plus the combined total amount across every order on each row.
Write a query to return the ID and status of every order, plus the total number of orders on each row.
Write a query to return the ID, name, category, and price of every product, plus the average price across the product's category on each row.
Write a query to return the ID, status, and amount of every order, plus the total revenue across every order in that status on each row.
Write a query to return the ID and customer ID of every order, plus the total number of orders placed by that customer on each row.
Write a query to return the ID, user ID, and event count of every session, plus the average event count across that user's sessions on each row.
Write a query to return the ID, category ID, and price of every product, plus both the minimum and maximum prices across the product's category on each row.
Write a query to return the ID, status, and amount of every order, plus the running total of amounts within the order's status group up to and including that order on each row.
Write a query to return the ID, category ID, and price of every product, plus the catalog-wide average price and the category average price on each row.
Start learning to practice all 9 Window Functions problems, with instant grading and mastery tracking.
Deeper guides on Window Functions
- why OVER is a clause and not a function
It is a clause, not a function, and the difference explains the error you are getting.
Common questions about Window Functions
Do window functions reduce the number of rows?
No, and that is the whole point of them. Two hundred orders come back as two hundred rows with the total attached to each, where a GROUP BY would collapse them into one. Reach for a window when you need the summary and the detail together.
Can one query use several window functions at once?
Yes, and they can use different windows. A count over everything, a sum over everything and a row number in a chosen order can all sit in the same SELECT list, each with its own OVER clause.
Can a window function be used in HAVING?
No. HAVING is resolved before windows are computed, so PostgreSQL rejects it outright rather than producing a wrong answer. Compute the window in a subquery or a CTE and filter on the result outside, which is the same fix WHERE needs.
