GROUP BY: the concept that trips everyone up

GROUP BY says: split the rows into piles, then give me one row per pile. Once you hold that picture, the rules stop being arbitrary — every one of them follows from it.

The picture

SELECT shelf, count(*) AS titles, sum(copies) AS copies
FROM books
GROUP BY shelf
ORDER BY shelf
LIMIT 8;

 shelf | titles | copies
-------+--------+--------
 B-01  |      2 |      4
 B-02  |      2 |      2
 C-01  |      3 |      9
 C-02  |      1 |      6
 F-01  |      3 |      9
 F-02  |      2 |      4
 F-03  |      2 |      7
 F-04  |      2 |      4
(8 rows)

Forty rows went in. They were sorted into piles by shelf. Each pile produced one row, with count(*) and sum(copies) computed within that pile.

Everything else in this lesson is a consequence.

The rule, and the error everybody hits

Every column in SELECT must either be in the GROUP BY, or be inside an aggregate.

SELECT shelf, title, count(*) FROM books GROUP BY shelf;

ERROR:  column "books.title" must appear in the GROUP BY clause or be used in an
        aggregate function
LINE 1: SELECT shelf, title, count(*) FROM books GROUP BY shelf;
                      ^

Shelf F-01 has three books. You asked for one row for that pile and also for a title — but there are three titles in the pile and no rule for which one to print. The database refuses rather than guessing.

Three ways out, depending on what you actually wanted:

-- 1. one row per shelf, with the titles collected
SELECT shelf, count(*), string_agg(title, ', ' ORDER BY title) AS titles
FROM books GROUP BY shelf;

-- 2. one row per shelf, picking a representative deliberately
SELECT shelf, count(*), max(title) AS a_title FROM books GROUP BY shelf;

-- 3. you did not want grouping at all — you wanted a count beside every row
SELECT shelf, title, count(*) OVER (PARTITION BY shelf) AS on_this_shelf FROM books;

The third is a window function, and it is the answer far more often than people realise: it keeps every row and adds an aggregate. Module 5 touches on them.

MySQL historically allowed the broken query and returned an arbitrary title, which is why you will meet people who think PostgreSQL is being fussy. It is not; the other behaviour was hiding a bug.

Grouping by more than one column

SELECT membership, pincode, count(*)
FROM members
GROUP BY membership, pincode
ORDER BY membership, pincode
LIMIT 8;

 membership | pincode | count
------------+---------+-------
 senior     | 411004  |     1
 senior     | 411021  |     1
 senior     | 411052  |     1
 standard   | 411004  |     1
 standard   | 411021  |     3
 standard   | 411029  |     2
 standard   | 411038  |     6
 standard   | 411052  |     2
(8 rows)

One pile per combination that actually occurs. Combinations with no rows do not appear — there is no "student, 411029, 0" line, because grouping can only produce rows from rows that exist.

That is worth knowing when a report has gaps: GROUP BY cannot invent zeros. Getting them requires joining against a list of all the values you expect, which module 5 covers.

Where GROUP BY sits

FROM        get the rows
WHERE       throw some away          ← before grouping
GROUP BY    make the piles
HAVING      throw some piles away    ← after grouping
SELECT      compute the output
ORDER BY    sort the result
LIMIT       take some

Two consequences you will use constantly:

WHERE cannot see an aggregate. It runs before the piles exist.

SELECT shelf, count(*) FROM books WHERE count(*) > 2 GROUP BY shelf;
-- ERROR:  aggregate functions are not allowed in WHERE

That is HAVING's job, and it is the next lesson.

ORDER BY and HAVING can use a SELECT alias; WHERE and GROUP BY cannot. Same reason as module 2 — the alias does not exist until SELECT runs. (PostgreSQL does permit an alias in GROUP BY as an extension; do not rely on it.)

Grouping by an expression

SELECT EXTRACT(YEAR FROM borrowed_on) AS year, count(*)
FROM loans
GROUP BY EXTRACT(YEAR FROM borrowed_on)
ORDER BY year;

Repeat the expression in both places. You can also use the ordinal position:

GROUP BY 1

which means "the first SELECT column". It is terse, it is legal, and it makes the query fragile — add a column at the front and the grouping silently changes. Acceptable at the prompt; write the expression out in anything that ships.

For dates, date_trunc is usually what you want:

SELECT date_trunc('month', borrowed_on) AS month, count(*)
FROM loans GROUP BY 1 ORDER BY 1;

Counting per group, correctly

The mistake that produces wrong numbers rather than errors:

SELECT m.name, count(*) AS loans
FROM members m JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name;

Note GROUP BY m.id, m.name rather than GROUP BY m.name. Two members could share a name, and grouping by the name alone would merge them into one row with a combined count. Grouping by the primary key cannot.

PostgreSQL has a convenience here: because m.id is the primary key, grouping by it alone lets you select any other column of members without listing it:

SELECT m.id, m.name, m.email, count(*) FROM members m JOIN loans l ON l.member_id = m.id
GROUP BY m.id;

That is functional dependency, it is standard SQL, and it is genuinely useful for wide tables. It only works when you group by the primary key.

Rule: group by the key, not by the label.

GROUP BY produces no row for an empty group

SELECT shelf, count(*) FROM books WHERE copies > 100 GROUP BY shelf;

Zero rows — not one row saying zero. WHERE removed everything before the piles were made, so there are no piles.

Compare with the no-GROUP BY form:

SELECT count(*) FROM books WHERE copies > 100;    -- one row, containing 0

With GROUP BY, no rows means no groups. Without it, you always get exactly one row. Application code that expects a row and gets none is a common small bug.

Check your work

The picture. Split rows into piles; one row per pile.

The rule. Every SELECT column is in the GROUP BY or inside an aggregate.

Why the database refuses rather than picking a title. Three titles in the pile and no rule for choosing.

Three ways out. string_agg to collect them, an aggregate like max() to choose deliberately, or a window function to keep every row.

What grouping by two columns gives. One pile per combination that actually occurs.

What GROUP BY cannot do. Invent zeros for combinations with no rows.

Why WHERE cannot use an aggregate. It runs before the piles exist.

Which clauses can use a SELECT alias. ORDER BY and HAVING; not WHERE.

Why GROUP BY 1 is risky. Adding a column at the front silently changes the grouping.

Why group by m.id, m.name and not m.name. Two people can share a name.

What functional dependency lets you do. Group by the primary key and select any other column of that table.

What you get from a GROUP BY that matches nothing. No rows — not a row containing zero.

Practice

1. Count the books on each shelf. Then sum the copies per shelf.
2. Run the shelf, title, count(*) query and read the error carefully.
3. Fix it three ways: string_agg, max(title), and a window function.
4. Group members by membership and by pincode together. Find a combination that does not appear and explain why there is no zero row for it.
5. Try WHERE count(*) > 2 and read the error.
6. Group loans by year with EXTRACT, then by month with date_trunc.
7. Use GROUP BY 1, then add a column at the front of the SELECT and see what changed.
8. Count loans per member grouping by m.name only. Then by m.id, m.name. Insert a second member with an existing name and run both again.
9. Group by m.id alone and select m.email as well. Explain why that is allowed.
10. Write a GROUP BY whose WHERE matches nothing. Then the same query without GROUP BY. Note the row counts.
11. Find the shelf with the most copies, using ORDER BY and LIMIT.
12. Count loans per membership type, and add the average fine per type in the same query.

Official documentation

• PostgreSQL — GROUP BY and HAVING — Where grouping sits in the evaluation order.
• PostgreSQL — SELECT reference — Including the functional-dependency rule for grouping by a primary key.
• PostgreSQL — Window functions — Keeping every row and adding an aggregate beside it.
• PostgreSQL — Date/time functions — date_trunc and EXTRACT for grouping by time period.

Next: HAVING, and why it is not WHERE.