Counting and grouping

One number from many rows

An aggregate function folds a column of values into a single answer. Counting with the wildcard counts the rows — every row, NULLs included — while COUNT(series) counts only the rows where series is set. SUM, AVG, MIN and MAX do what their names say.

On their own they answer one question about the whole table: how many books there are, the oldest year, the average year. The result is a single row with a single column, and naming that column with AS makes the answer readable.

GROUP BY folds rows into buckets

GROUP BY author sorts the rows into one bucket per author and then applies the aggregates to each bucket separately — one result row per author, with that author's count next to the name. That is the whole idea: one row in, one row out, per group.

Every column in the SELECT must either be one of the grouped columns or wrapped in an aggregate. The query below asks for the author and a count — a name and a number per bucket — and nothing that would need to be picked arbitrarily from inside the bucket.

WHERE before, HAVING after

WHERE filters rows before they are grouped; HAVING filters the groups after the counting. "Books published after 1960, grouped by author" uses WHERE year > 1960; "only authors with more than one such book" adds a HAVING clause that tests the count as a number.

Using WHERE when you mean HAVING is one of the classic errors, and the database will tell you about it — an aggregate cannot be tested before it has been computed.

What the aggregates see

COUNT(*) counts rows. COUNT(column) counts the rows where that column is not NULL, and COUNT(DISTINCT column) counts the distinct values that are there. The difference is not cosmetic: five books with two missing series have five rows, five titles, and three recorded series.

SUM, AVG, MIN and MAX also skip NULLs. AVG(column) divides by the number of non-NULL values, not by the number of rows, so a column with missing values reports the average of the values it has. SUM over no rows is NULL, while COUNT over no rows is 0 — one means "nothing to add", the other "none found".

An aggregate without GROUP BY always answers with exactly one row, even when the table is empty. That row is the answer to "how many", not a list of the rows it counted.

The rule behind GROUP BY

Once rows are folded into a group, a bare column has no single value to show. Standard SQL therefore requires every selected column to be grouped or aggregated. SQLite is looser: it lets you select any column and quietly picks a value from one row of the group — usually the last one it read, which is not a promise.

That looseness is a portability trap. A query that works in SQLite can fail in PostgreSQL with "column must appear in the GROUP BY clause", and the value it picked in SQLite was arbitrary anyway. Write the grouped column or an aggregate, and the query means the same thing everywhere.

GROUP BY can name more than one column, and the groups are the combinations. GROUP BY author, year gives one row per author per year, which is a different report from one row per author. NULLs form a group of their own.

It does not promise an order, either. Groups come back in whatever order the database produced them, which is often the order it first met each one — not alphabetical, and not the order the query listed them in. ORDER BY is the only clause that decides the order of the answer.

A count per group, filtered

SELECT author, COUNT(*) AS books
FROM books
WHERE year > 1950
GROUP BY author
HAVING COUNT(*) > 1
ORDER BY books DESC, author;

Try these next

• Count the distinct authors in the table and compare the answer with COUNT(author).
• Group by author and year, then keep only the pairs with more than one book.
• Add HAVING SUM(year) > 4000 and explain which groups survive and why.