Counting & Grouping

"How many books do we have?" You do not want the librarian to wheel out every card and make you count the pile. You want a number: "12,408." Some questions aren't about the cards at all — they're about the pile. SQL calls the tools for those questions aggregates, and they change the shape of what comes back.

Aggregates: one answer about many rows

An aggregate function takes a whole column of values and boils it down to a single result. The two you'll use constantly:

SELECT COUNT(*)
FROM loans;

COUNT(*)
--------
128

COUNT(*) — how many rows are there? (The * here means "count the cards themselves," no particular column.) And:

SELECT AVG(age)
FROM students;

AVG(age) — add up every value in the column, divide by how many. Cousins exist and read exactly as you'd hope — SUM (total), MIN (smallest), MAX (largest) — one idea, five spellings.

Notice something structural: the answer is still a table, as always, but it has one row, and that row describes the pile, not any card in it. WHERE still works beautifully upstream — count only the overdue loans:

SELECT COUNT(*)
FROM loans
WHERE due < '2026-08-01';

Test the cards first, then summarize only the survivors.

GROUP BY: a summary per group

Now the genuinely new idea. "How many loans per member?" is several counts — one per member. You could run one query per member and staple the answers together; by member forty you'd have opinions about that. GROUP BY does it in one ask:

SELECT member_id, COUNT(*)
FROM loans
GROUP BY member_id;

member_id | COUNT(*)
----------+---------
1         | 3
2         | 7
3         | 1

Picture the librarian sorting the loan cards into stacks on the counter, one stack per member, then writing one summary line per stack. That's GROUP BY: fold the rows into groups by the column you name, then run the aggregate once per group. One row back per group — a table of summaries.

The classic stumble lives right here, so meet it now: once you group, each answer row speaks for a whole stack — so you may only SELECT things that make sense per-stack: the grouping column itself, and aggregates. SELECT member_id, due, COUNT(*) grouped by member is incoherent — a stack of seven loans has seven due dates, and the librarian can't write seven values in one blank. If you catch yourself asking for a per-card fact in a per-stack answer, one of the two has to give.

Everything earlier still composes: ORDER BY COUNT(*) DESC LIMIT 1 on the query above hands you the library's most enthusiastic borrower, in one breath.

One thing keeps these summaries slightly anonymous, though: "member 2" is a pointer, not a person. Their name lives in a different drawer — and pulling from two drawers in one question is exactly the finale this unit has been building toward. Next lesson: JOIN.