Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
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 need a calculation across related rows—such as a department total or rank—while keeping each original row in the result.
The examples below use PostgreSQL syntax. Other database products may differ in supported functions or details.
How GROUP BY changes your results
Imagine a sales table with one row per employee sale:
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →| department | employee_id | employee | amount |
|---|---|---|---|
| Sales | 101 | Ana | 120 |
| Sales | 102 | Ben | 90 |
| Support | 201 | Cam | 75 |
To return the total for each department, group by department:
#1 Best Overall
SELECT department, SUM(amount) AS department_total
FROM sales
GROUP BY department;The output has one row per department. The individual employee rows are no longer present because GROUP BY changes the result’s grain: rows with matching grouping values are summarized together. PostgreSQL’s aggregate tutorial explains this grouping behavior.
How a window function keeps detail rows
If you want each employee’s sale alongside the department total, calculate the total over each department with SUM as a window function:
SELECT department, employee_id, employee, amount,
SUM(amount) OVER (PARTITION BY department) AS department_total
FROM sales;This returns the employee-level rows and repeats the department total on each row in that department. PostgreSQL’s documentation puts the distinction plainly: “However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would.” See PostgreSQL’s window-function tutorial.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →OVER marks the calculation as a window function. Inside it, PARTITION BY department defines which rows are considered together for the calculation. It does not collapse those rows into a single output row.
What PARTITION BY and ORDER BY mean
PARTITION BY is the window-function counterpart to defining calculation groups; it is not the same operation as GROUP BY. The key difference is what happens to the rows: GROUP BY summarizes groups into fewer rows, while PARTITION BY sets the boundaries for a calculation that retains the rows.
Add ORDER BY inside OVER when a calculation depends on sequence, such as ranking or a running total. This ordering controls the window calculation; it does not guarantee the final order of the query output. To order the displayed result, use a separate query-level ORDER BY.
Rank rows within each department
To number employees from highest to lowest sale in 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 employee_id as a tie-breaker makes the ordering deterministic if two employees have the same amount, assuming that ID is unique. Without a tie-breaker, PostgreSQL assigns tied ordering values in unspecified order.
Filter a window result in PostgreSQL
In PostgreSQL, a window function cannot be used directly in WHERE. Calculate the rank in a subquery, then filter it from the outer query to return, for example, the top two 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 <= 2
ORDER BY department, department_rank;PostgreSQL evaluates window functions over the virtual table remaining after FROM, WHERE, GROUP BY, and HAVING; ordinary aggregates are evaluated before window functions. That is why the subquery pattern works: the outer query can filter the rank after the inner query has calculated it. The PostgreSQL documentation describes this processing order and filtering constraint.
Choose based on the result you need
- Choose
GROUP BYfor a compact summary, such as one total or average per department. - Choose a window function for a per-row comparison, rank, or running calculation where the underlying rows still matter.
- Use both when you need grouped aggregates and then a window calculation over the grouped result; in PostgreSQL, window calculations follow ordinary aggregation.
These examples show the difference in output shape, not which approach runs faster. No performance comparison is established here. Check your database engine’s documentation for dialect-specific syntax and behavior.
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.
