The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Use GROUP BY when you want to collapse rows into a summary, such as one sales total per department. Use a window function when you want to calculate a department total, rank, or running value while keeping each employee’s row in the result. In PostgreSQL, you can also combine them: window functions operate on the rows left after grouping and ordinary aggregation.
How GROUP BY changes your result
Suppose a sales table has one row per employee sale, with columns for department, employee_id, employee, and amount. To get one total for each department in PostgreSQL, write:
As an Amazon Associate I earn from qualifying purchases.
SELECT department, SUM(amount) AS department_total
FROM sales
GROUP BY department;The query returns one row per department. Individual sale and employee rows are no longer present in the result; SUM has summarized their amounts. This change in the level of detail is called a change in the result’s grain.
Free tools Windows power users keep installed
One-click scans. No signup required.
How a window function keeps detail rows
To show each employee’s amount alongside the total for that employee’s department, use a window function:
#1 Best Overall
SELECT department, employee_id, employee, amount,
SUM(amount) OVER (PARTITION BY department) AS department_total
FROM sales;The department total appears on every matching employee row, so the result retains employee-level detail as well as the group calculation. PostgreSQL’s documentation explains: “However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would.” PostgreSQL documentation: Window Functions
What OVER, PARTITION BY, and ORDER BY mean
OVER marks a function as a window function. Inside it, PARTITION BY defines which related rows the function calculates across. It is conceptually similar to grouping for the calculation, but it does not collapse those rows in the output.
For example, PARTITION BY department creates a separate calculation partition for each department. Add ORDER BY inside OVER when the calculation depends on row order, such as a rank or running total. That ordering controls the window calculation; it does not guarantee the order in which the query’s final rows are displayed. Use a query-level ORDER BY if you need a particular output order.
Recommended Free Tools
Use a window function to rank rows
To number employees from highest to lowest amount within each department, use ROW_NUMBER:
SELECT department, employee_id, employee, amount,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY amount DESC, employee_id
) AS department_rank
FROM sales;The numbering restarts for each department. Including a unique tie-breaker such as employee_id makes the order deterministic when employees have equal amounts. Without a tie-breaker, PostgreSQL does not specify which tied row receives which row number.
Filter a window result in an outer query
In PostgreSQL, you cannot refer to a window result directly in the same query’s WHERE clause. Calculate the rank in a subquery, then filter in the outer query to return, for example, the top three employees per department:
SELECT department, employee_id, employee, amount, department_rank
FROM (
SELECT department, employee_id, employee, amount,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY amount DESC, employee_id
) AS department_rank
FROM sales
) AS ranked_sales
WHERE department_rank <= 3
ORDER BY department, department_rank;The inner query calculates a rank for every employee. The outer query can filter those calculated values because they are now columns in its input.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
How GROUP BY and window functions fit together
These techniques are not mutually exclusive. PostgreSQL evaluates window functions after FROM, WHERE, GROUP BY, and HAVING, and after ordinary aggregate calculations. A window function therefore works over the virtual table that remains at that stage, including rows already summarized by grouping if the query uses GROUP BY.
Choose based on the result you need: a smaller summary with one row per group calls for GROUP BY; a calculation attached to rows you still need to see calls for a window function. The examples show output shape, not relative speed; they are not a performance comparison.
PostgreSQL scope
The syntax and behavior described here are grounded in the PostgreSQL 18 documentation. Other database products can differ in supported functions and syntax, so check your database engine’s documentation before adapting a query.
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.
