Lesson Description
One row per group instead of one per input row.
Lesson 02
Lesson Description
One row per group instead of one per input row.
Easy Project
Mini project: Produce an executive KPI table with totals and averages per department, sorted by highest business impact.
In the previous lesson every aggregate query returned a single row: one headcount, one payroll total, one average. That answers "how many employees do we have?" but not "how many employees does each department have?" To answer the second question without GROUP BY, you would have to run one query per department and paste the results together.
GROUP BY does that for you. It splits the rows into buckets that share the same value in one or more columns, then runs your aggregates once per bucket. The output has one row per group instead of one row for the whole table. This is the workhorse of reporting: sales by month, patients by city, orders by status.
SELECT grouping_column, AGGREGATE_FUNCTION(expression) AS alias
FROM table_name
WHERE optional_row_filter
GROUP BY grouping_column
ORDER BY optional_sort;
You can group by several columns (GROUP BY a, b) or by an expression (GROUP BY DATE_TRUNC('month', order_date)).
The hospital's outreach team wants to know where its patients live.
SELECT city, COUNT(*) AS patients
FROM patients
GROUP BY city
ORDER BY patients DESC, city;
This returns 5 rows, one per distinct city:
| city | patients |
|---|---|
| Seattle | 10 |
| Portland | 7 |
| Spokane | 5 |
| Bellevue | 4 |
| Tacoma | 4 |
PostgreSQL took the 30 patient rows, sorted them into five buckets by city, and ran COUNT(*) inside each bucket. The counts add back up to 30, which is a good sanity check whenever you write a grouped count.
HR wants salary statistics for every department, highest average first.
SELECT
department_id,
COUNT(*) AS headcount,
ROUND(AVG(salary), 2) AS avg_salary,
MAX(salary) AS top_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;
You get 9 rows. Department 90 tops the list with 3 people averaging 19333.33, followed by department 80 (9 people, 10833.33). Department 30 comes last at 4150.00, even though its top salary is 11000.00: one well-paid manager and five lower-paid clerks pull the mean down.
Why 9 rows when the departments table has 10? Department 110 has no employees, so there are no rows in employees to form a group. GROUP BY can only group rows that exist. Showing empty departments with a zero count requires joining to departments, which you will learn in the Joins course.
Notice also that ORDER BY avg_salary uses the alias. That works because ORDER BY runs after SELECT, as you will see below.
Within each department, how many people hold each job?
SELECT department_id, job_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;
This returns 14 rows. A group is now a unique combination of department_id and job_id. Department 30, for example, appears twice: once for job 4 (1 person) and once for job 5 (5 people). Adding a column to GROUP BY can only split groups further, never merge them, so you always get at least as many rows as grouping by either column alone.
Grouping by a raw order_date would give you one group per calendar day, which is too granular for a monthly report. Group by the month instead:
SELECT
DATE_TRUNC('month', order_date)::date AS order_month,
COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;
Five rows come back, January through May 2025, with 7, 10, 10, 13 and 10 orders. DATE_TRUNC('month', ...) rounds every date down to the first of its month, so all April dates land in the same bucket. The ::date cast just strips the time portion for display.
PostgreSQL also lets you write GROUP BY order_month here, referring to the output alias, and the result is identical. That is a PostgreSQL convenience; repeating the expression is the portable form.
You can group by computed booleans too. Here patients are split by gender and by whether they were born in 2000 or later:
SELECT
EXTRACT(YEAR FROM date_of_birth) >= 2000 AS born_2000_or_later,
gender,
COUNT(*) AS patients
FROM patients
GROUP BY 1, 2
ORDER BY 1, 2;
Four rows: 13 women and 12 men born before 2000, 2 women and 3 men born after. GROUP BY 1, 2 means "the first and second items in the SELECT list."
How many direct reports does each manager have?
SELECT manager_id, COUNT(*) AS direct_reports
FROM employees
GROUP BY manager_id
ORDER BY direct_reports DESC, manager_id;
This returns 10 rows. Manager 100 has 14 direct reports, the most by far. One row has manager_id = NULL with a count of 1: that is the President, who reports to nobody.
This is worth pausing on. In WHERE, NULL = NULL is not true. But GROUP BY treats all NULLs as belonging to the same group. If you do not want that bucket in your report, filter it out with WHERE manager_id IS NOT NULL before grouping.
SQL is written in one order but logically processed in another:
| Step | Clause | What happens |
|---|---|---|
| 1 | FROM | Read the source rows |
| 2 | WHERE | Throw away rows that fail the condition |
| 3 | GROUP BY | Sort the surviving rows into groups |
| 4 | Aggregates | Compute COUNT, SUM, etc. per group |
| 5 | SELECT | Build one output row per group, apply aliases |
| 6 | ORDER BY | Sort the output rows |
| 7 | LIMIT | Cut the list |
Two consequences follow. First, once step 3 runs, individual rows are gone; every expression in SELECT must produce a single value per group. That gives you the golden rule: every column in SELECT must either appear in GROUP BY or be inside an aggregate. Second, aliases defined in step 5 are visible in ORDER BY but not in WHERE.
There is one exception to the golden rule that PostgreSQL implements: if you group by a table's primary key, you may select any other column of that table, because each key value identifies exactly one row. SELECT id, first_name, COUNT(*) FROM employees GROUP BY id is accepted. This becomes handy once you start joining tables.
SELECT department_id, first_name, COUNT(*)
FROM employees
GROUP BY department_id;
ERROR: column "employees.first_name" must appear in the GROUP BY clause
or be used in an aggregate function
Department 80 has nine people. Which first_name should its single output row show? Decide what you actually want: add first_name to GROUP BY (which gives one row per name per department), wrap it in an aggregate such as MIN(first_name), or drop it.
Positional grouping is concise but fragile. Suppose you add a label at the front of the list:
SELECT 'all' AS label, status, COUNT(*)
FROM appointments
GROUP BY 1;
ERROR: column "appointments.status" must appear in the GROUP BY clause
or be used in an aggregate function
Position 1 is now the constant 'all', not status. Prefer column names in anything you will maintain, and use positions only for quick exploration.
PostgreSQL often uses a hash table to build groups, so rows can come back in any order. If order matters (and in a report it always does), add an explicit ORDER BY.
Grouping by a unique column such as id produces one group per row, and every count is 1. If your grouped output has as many rows as the table, you probably grouped by the wrong thing.
SELECT and picked an arbitrary value. Since 5.7 the ONLY_FULL_GROUP_BY mode is on by default and it behaves like PostgreSQL.GROUP BY 1 positional references or output aliases in GROUP BY; you must repeat the expression.The exercises below ask for one row per group across all three sandboxes: employees per department, orders per status, products per category, and appointments per status. Two of them require a specific sort order, so check the prompt and remember that GROUP BY alone does not sort.
GROUP BY produces one output row per distinct value (or combination of values) of the grouping columns.SELECT column must be grouped or aggregated; PostgreSQL allows an exception when you group by a primary key.DATE_TRUNC('month', order_date) to build time series.GROUP BY does not guarantee order; always add ORDER BY when order matters.GROUP BY splits rows into groups and runs the aggregates once per group, which is how you get headcount per department, revenue per month or appointments per doctor. The key idea is grain: each output row represents one value of the GROUP BY columns.
Headcount and average salary per department
SELECT d.name AS department,
COUNT(*) AS headcount,
ROUND(AVG(e.salary), 0) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
ORDER BY headcount DESC;One row per department. Every non-aggregated column in SELECT must appear in GROUP BY.
Grouping by a computed value
SELECT DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;Rounding dates down to the month puts every order from the same month into one group.
GROUP BY department_id, job_id?One row per distinct department and job combination, so a department with three job types produces three rows.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return department_id and employee_count for every department in the HR sandbox.
No results yet
Run a query to see results here
From the store sandbox, return status and the number of orders (order_count) in each status.
No results yet
Run a query to see results here
From the store sandbox, return category_id and product_count (the number of products in each category), ordered by category_id.
No results yet
Run a query to see results here
From the hospital sandbox, return each appointment status and the number of appointments in that status (appt_count), ordered by status.
No results yet
Run a query to see results here