Skip to main content
0%

GROUP BY

0/4 solved4 to unlock next
PrevLocked

Lesson 02

GROUP BY

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.

Syntax

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)).

Worked examples

1. One grouping column

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:

citypatients
Seattle10
Portland7
Spokane5
Bellevue4
Tacoma4

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.

2. Several aggregates per group

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.

3. Grouping by two columns

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.

4. Grouping by an expression

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."

5. NULL forms its own group

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.

How PostgreSQL evaluates it

SQL is written in one order but logically processed in another:

StepClauseWhat happens
1FROMRead the source rows
2WHEREThrow away rows that fail the condition
3GROUP BYSort the surviving rows into groups
4AggregatesCompute COUNT, SUM, etc. per group
5SELECTBuild one output row per group, apply aliases
6ORDER BYSort the output rows
7LIMITCut 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.

Common mistakes

Selecting a column that is not grouped

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.

GROUP BY 1 pointing at the wrong column

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.

Assuming GROUP BY sorts the output

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 too much

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.

How other databases differ

  • MySQL historically allowed non-grouped columns in SELECT and picked an arbitrary value. Since 5.7 the ONLY_FULL_GROUP_BY mode is on by default and it behaves like PostgreSQL.
  • SQL Server does not allow GROUP BY 1 positional references or output aliases in GROUP BY; you must repeat the expression.
  • SQLite is permissive: it accepts bare columns next to aggregates and returns a value from some row in the group. Do not rely on it.

Practice

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.

Key takeaways

  • GROUP BY produces one output row per distinct value (or combination of values) of the grouping columns.
  • Every SELECT column must be grouped or aggregated; PostgreSQL allows an exception when you group by a primary key.
  • You can group by expressions such as DATE_TRUNC('month', order_date) to build time series.
  • All NULLs fall into a single group.
  • GROUP BY does not guarantee order; always add ORDER BY when order matters.

GROUP BY: in depth

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.

Worked examples

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.

Common mistakes

  • Grouping by a name that is not unique (two departments called "Sales" would merge). Group by the id and the name together.
  • Adding an extra column to GROUP BY to make an error go away, which silently changes the grain and splits your groups.
  • Expecting groups in sorted order. GROUP BY does not guarantee any order; add ORDER BY.

Other databases

  • PostgreSQL lets you GROUP BY a primary key and then select other columns of that table; most other databases require listing them.
  • DATE_TRUNC is PostgreSQL-specific. MySQL uses DATE_FORMAT(order_date, '%Y-%m-01') and SQL Server 2022+ uses DATETRUNC(month, order_date).
Check yourself: What is the grain of 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

Quick check: GROUP BY

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Employee count per department

Enterprise Workforce AnalyticsmediumPostgreSQL

Return department_id and employee_count for every department in the HR sandbox.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Orders by status

Commerce Operations IntelligencemediumPostgreSQL

From the store sandbox, return status and the number of orders (order_count) in each status.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Product count per category

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return category_id and product_count (the number of products in each category), ordered by category_id.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

Appointments per status

Clinical Care PerformanceeasyPostgreSQL

From the hospital sandbox, return each appointment status and the number of appointments in that status (appt_count), ordered by status.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

0%

GROUP BY

0/4 solved4 to unlock next
PrevLocked