What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Use an ordinary aggregate when you want a summary row for a set or group; use a window function when you want a calculation across related rows while keeping each detail row in the result. The same aggregate, such as AVG or SUM, can do either job: adding OVER (...) makes it a window calculation.
How the results differ
A grouped aggregate summarizes its input. With GROUP BY, the result has one row for each group rather than one row for each original employee. A window function calculates across related rows and attaches its result to each row, preserving the row-level details.
PostgreSQL’s window-function tutorial describes a window function as calculating across rows related to the current row. That distinction is useful when deciding whether your output should be a summary or detailed records accompanied by a summary.
GROUP BY versus PARTITION BY
GROUP BY department creates department-level groups for aggregation. PARTITION BY department, inside an OVER clause, divides rows into calculation sets but does not collapse them. They are not interchangeable: GROUP BY shapes the output rows; PARTITION BY shapes which rows a window calculation relates to.
#1 Best Overall
Same average, different output
Grouped average: one result per department
SELECT department, AVG(salary) AS department_avg
FROM employees
GROUP BY department;This returns a department and its average salary for each department. Individual employee rows are not represented in the grouped result.
Window average: average beside every employee
SELECT department, employee_id, salary,
AVG(salary) OVER (PARTITION BY department) AS department_avg
FROM employees;This returns employee rows and repeats the relevant department average beside each one. PostgreSQL’s tutorial shows this row-preserving behavior. The examples illustrate the distinction; they are not represented as executed or tested queries.
When to choose each approach
| Question | Ordinary aggregate | Window function |
|---|---|---|
| Should individual detail rows remain in the result? | Usually not in grouped output. | Yes; the calculation is added to the rows. |
| What defines the calculation groups? | GROUP BY. |
PARTITION BY inside OVER. |
| Do you need ranking, ordering, a running result, or a moving frame? | Usually not for ordinary grouping. | Often; these calculations depend on window ordering or framing. |
| Do you want detail and a related summary side by side? | Not directly in a simple grouped result. | Yes. |
These are practical defaults, not limits on what a query can combine. A query can aggregate at one stage and apply a window calculation at another; exact syntax depends on the database.
How OVER, ordering, and frames affect a window
An aggregate such as SUM(amount) computes over a set or group in ordinary use. Written as SUM(amount) OVER (...), it computes a window value instead. MySQL 8.4 documents many aggregate functions as usable with or without OVER, and PostgreSQL demonstrates the same pattern with AVG.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →An ORDER BY inside OVER controls the calculation order; it does not sort the final query output. A frame can further limit which rows contribute. In PostgreSQL, when a window has ORDER BY and no explicit frame overrides the default, the frame runs from the start of the partition through the current row and includes peers with equal ordering values. As a result, rows tied on the ordering value can have the same cumulative result.
For a running total or moving calculation, specify the intended ordering and frame when they affect the answer, and check the syntax for your database. For repeatable rankings among tied values, include a deterministic tie-breaker in the window ordering.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Filtering a window result
In PostgreSQL, window functions are evaluated after WHERE, GROUP BY, HAVING, and ordinary aggregates. A window result therefore cannot be used in that query’s WHERE clause. Calculate it in a subquery or common table expression, then filter in the outer query:
SELECT department, employee_id, salary, rn
FROM (
SELECT department, employee_id, salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC, employee_id
) AS rn
FROM employees
) AS ranked
WHERE rn <= 3;This pattern numbers employees within each department by descending salary, then keeps the first three rows per department. The employee_id ordering provides a tie-breaker. The example is illustrative, not a tested query.
Best Value
Check your database’s syntax and support
Window and analytic functions are documented in PostgreSQL 18, MySQL 8.4, Microsoft Transact-SQL, and Oracle Database 19c, but supported functions, syntax, frame options, and defaults vary by engine and version. Microsoft notes that support for ORDER BY, ROWS, and RANGE depends on the function; MySQL documents syntax cases that differ from standard SQL. Consult the documentation for the database and version you actually use: PostgreSQL, MySQL 8.4, Microsoft Transact-SQL, and Oracle Database 19c.
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.
