Use GROUP BY when you want to collapse detail rows into summaries; use a window function when you want a calculation alongside the rows that produced it. The key difference is output shape: grouping changes the query’s row grain, while OVER (...) adds a result at the existing query-row grain.
Window functions vs. aggregate functions at a glance
| Question | Aggregate with GROUP BY |
Window function |
|---|---|---|
| What happens to rows? | Detail rows are combined into one result row per group. | Each query row remains; the calculation appears alongside it. |
| Typical syntax | AVG(salary) with GROUP BY department |
AVG(salary) OVER (PARTITION BY department) |
| Best for | Summaries such as revenue by country or average salary by department. | Row-level context, rankings, running totals, moving calculations, and comparisons within a group. |
| Filter the result | Use HAVING to filter groups. |
Compute the window result in a subquery or CTE, then filter in an outer query. |
| Portability | Check function support and syntax for your database. | Check function and window-frame support for your database and version. |
PostgreSQL defines a window function as a calculation across rows related to the current row. Its window functions documentation illustrates the practical distinction: a department average can either replace employee detail with one row per department or appear beside every employee row.
What changes when you use GROUP BY?
An ordinary aggregate such as AVG, SUM, or COUNT summarizes a set of values. When you use it with GROUP BY, the output has one row per group, not one row per original record.
SELECT department, AVG(salary) AS department_avg
FROM employees
GROUP BY department;This query returns a department and its average salary for each department. Individual employee rows are no longer present in the result.
#1 Best Overall
What does a window function preserve?
A window function performs a calculation over related rows but keeps each row in the query result. That makes it useful when you need a group-level value without losing the detail behind it.
SELECT department, employee_id, salary,
AVG(salary) OVER (PARTITION BY department) AS department_avg
FROM employees;This returns each employee’s department, ID, and salary, plus the average salary for that employee’s department. The same department average is repeated on each employee row in that department.
How PARTITION BY differs from GROUP BY
PARTITION BY divides rows into calculation groups inside a window; it does not collapse those rows. Think of it as defining the rows that contribute to a result for each current row. GROUP BY, by contrast, changes the output to one row for each group.
- Use
GROUP BY: “Show me one total per department.” - Use
PARTITION BY: “Show me each employee and the total for that employee’s department.”
In MySQL 8.4, an empty OVER() uses all rows in the query as one partition, so a global calculation is repeated for each row. See the MySQL 8.4 reference for its syntax and examples.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Read the parts of OVER (…)
OVERmarks an aggregate call used as a window function in PostgreSQL and MySQL syntax.PARTITION BYdefines the groups of rows used for the calculation without reducing the output rows.ORDER BYinsideOVERdefines the order used by the window calculation. It is separate from the query’s finalORDER BY, which sorts the returned output.- A frame can narrow an ordered window to a subset, such as the rows used for a running or moving calculation. Choose the frame deliberately and check your database’s default-frame behavior.
Choose the right approach for the task
Return a summary per group
Use an aggregate with GROUP BY when the detail rows are not needed—for example, one revenue total per country.
Keep the detail and add a group statistic
Use an aggregate window such as SUM(amount) OVER (PARTITION BY account_id) when every transaction should remain visible beside its account total.
Rank rows within a group
Use a ranking window function such as ROW_NUMBER() or RANK(), with an ORDER BY inside OVER to define the ranking order. Add PARTITION BY when the ranking should restart for each group.
Calculate a running or moving value
Use an aggregate window with an ordered window, and specify an appropriate frame for the rows included in each result. SQL Server’s OVER clause documentation describes uses such as running totals, cumulative aggregates, moving averages, and top-N-per-group queries.
Filtering: WHERE, HAVING, and window results
Window calculations run after the rows have passed through FROM, WHERE, GROUP BY, and HAVING. They can be used in the SELECT list and query-level ORDER BY, but not directly in WHERE, GROUP BY, or HAVING in PostgreSQL. To keep only rows with a computed rank, calculate it in a subquery or CTE and filter outside:
WITH ranked_employees AS (
SELECT department, employee_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS position
FROM employees
)
SELECT department, employee_id, salary, position
FROM ranked_employees
WHERE position = 1;The outer query can filter position because it is now a column produced by the inner query. MySQL 8.4 likewise places window processing after WHERE, GROUP BY, and HAVING, and before ORDER BY, LIMIT, and SELECT DISTINCT.
Can you combine aggregation and window functions?
Yes. A query can aggregate first, then use a window calculation across the grouped rows. For example, it can first produce total sales per country and then compare each country’s total with the overall total using a window calculation. PostgreSQL documents ordinary aggregates as valid arguments to a window function, but not the reverse: a window function cannot generally be nested inside an ordinary aggregate call. The order of operations matters because the window sees the query’s post-aggregation rows.
Check your database’s support
The core distinction is documented in PostgreSQL 18/current, MySQL 8.4, and Microsoft’s SQL Server documentation, but function availability and syntax vary by database and version. SQL Server’s aggregate-function documentation, for example, lists STRING_AGG, GROUPING, and GROUPING_ID as exceptions to aggregate functions that may take OVER. Do not assume every aggregate supports window use, or that frame options are identical across engines; consult the manual for the database and version running your query.
Recommended Free Tools
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
