Grouping rows

What does GROUP BY do?

The last lesson turned a whole table into one summary row. GROUP BY lets you get one summary row per category instead. GROUP BY branch sorts the loans into two piles, Central and Riverside, and every aggregate in the SELECT list is worked out separately for each pile.

Think of it as two steps. First the database puts rows with the same value in the grouping column into the same group. Then it produces one output row per group, using COUNT, SUM, AVG and the rest on the rows inside that group. Fourteen loans in, two rows out.

Open the Rows view and run the query below. The GROUP BY step shows the loans gathered under each branch, and the SELECT step shows each group folding into a single row with its count. Watching it once makes the idea stick far better than any description.

SELECT branch, COUNT(*) AS loans
FROM loans
GROUP BY branch;

How do you group by more than one column?

List several columns in GROUP BY, separated by commas. GROUP BY branch, genre makes one group for each combination that actually occurs: Central Fiction, Central Nature, Riverside Cookery and so on. Pairs with no loans, such as Central Hobbies, do not appear at all, because there are no rows to put in them.

The order of the columns in GROUP BY does not change which groups you get. GROUP BY genre, branch produces the same seven groups. The order of the output rows is a separate question, and the only reliable way to control it is ORDER BY.

Use the Rows view again with the demo solution. The GROUP BY step now shows seven smaller piles. If you are ever unsure what your groups are, a quick SELECT branch, genre, COUNT(*) with the same GROUP BY lists them all.

SELECT branch, genre, COUNT(*) AS loans
FROM loans
GROUP BY branch, genre;

Which columns are you allowed to select?

After grouping, each output row stands for a whole group. Every column in the SELECT list must therefore have one clear value per group. That leaves two kinds of column you can use: columns listed in GROUP BY, which are the same for every row in the group, and aggregates such as COUNT(*) or AVG(days_kept), which summarise the group.

Anything else is ambiguous. In the Central group, member_name has six different values. Which one belongs on the Central row? There is no right answer. PostgreSQL, SQL Server and MySQL in its default mode refuse the query with an error saying the column must appear in the GROUP BY clause or be used in an aggregate function.

Write the rule on a sticky note if you need to: in a grouped query, every selected column is either grouped or summarised. If you want a member name per group, ask which one you mean. The first alphabetically is MIN(member_name). The number of different members is COUNT(DISTINCT member_name).

SELECT branch,
  COUNT(*) AS loans,
  COUNT(DISTINCT member_name) AS members,
  MIN(member_name) AS first_member
FROM loans
GROUP BY branch;

Why does SQLite let bare columns through?

SQLite is lenient here. If you select a column that is neither grouped nor summarised, it runs anyway and fills that column with the value from one row of each group. The documentation calls these bare columns. Which row it uses is not something you should rely on.

That leniency is a trap. The query below looks reasonable and returns a tidy result with a book title on each branch row. The title is real data, so it looks believable. It means nothing: it is one arbitrary book from the group. Put that in a report and someone will read it as the most popular book at each branch.

Treat any column in a grouped query that is not in GROUP BY and not inside an aggregate as a bug, even though SQLite accepts it. AI tools produce this mistake often, especially when asked to "show the name too". The query will also fail outright the day it moves to PostgreSQL.

-- Runs in SQLite, but book_title is meaningless here
SELECT branch, book_title, COUNT(*) AS loans
FROM loans
GROUP BY branch;

How do you sort the groups?

ORDER BY runs after grouping, so it can sort by a grouped column, by an aggregate, or by the alias you gave an aggregate. ORDER BY loans DESC puts the biggest groups first. ORDER BY branch, loans DESC sorts by branch and then, within each branch, by count.

Without ORDER BY, SQLite often returns groups in the order of the grouping columns, because of the way it builds them. That is a side effect with no guarantee behind it, and other databases behave differently. If whoever reads the result needs a particular order, write it out.

Ties are common with counts. Two groups with the same count can come out in either order. Add a second sort column, such as the genre name, to make the order stable.

SELECT genre, COUNT(*) AS loans
FROM loans
GROUP BY genre
ORDER BY loans DESC, genre;

How does WHERE work with GROUP BY?

WHERE runs before grouping. It decides which rows go into the piles at all. WHERE branch = 'Riverside' throws away the Central loans first, so every count afterwards covers Riverside only.

The order of the steps is FROM, WHERE, GROUP BY, then SELECT and ORDER BY. That is also the order the Rows view shows. A filter on a single row, such as a branch, a date range or a genre, belongs in WHERE. A filter on a group's total, such as "genres with more than three loans", cannot go in WHERE, because the totals do not exist yet at that point. That needs HAVING, the subject of the next lesson.

SELECT genre, COUNT(*) AS loans, ROUND(AVG(days_kept), 1) AS avg_days
FROM loans
WHERE branch = 'Riverside'
GROUP BY genre
ORDER BY loans DESC;

Can you group by a calculation?

Yes. You can group by any expression as well as a plain column. Grouping by strftime('%Y-%m', loaned_on) gives one row per month. Grouping by days_kept > 14 gives two groups, long loans and short loans, plus a third group for NULL.

Repeat the same expression in SELECT so the output shows which group each row is. SQLite and PostgreSQL also let you group by the alias you gave it in SELECT, which saves typing. Some databases, such as SQL Server, do not, so repeating the expression is the portable choice.

NULL values form their own group. Every row with NULL in the grouping column lands in a single group, even though NULL is never equal to NULL in a WHERE condition. The two loans that are still out show up together in a group of their own when you group by days_kept.

SELECT strftime('%Y-%m', loaned_on) AS month, COUNT(*) AS loans
FROM loans
GROUP BY strftime('%Y-%m', loaned_on);

How can you check a grouped result is right?

Grouped results are easy to get almost right. A few quick checks catch most mistakes before anyone else sees them.

• The counts should add up. Total the loans column in your head or with a second query, and compare it with SELECT COUNT(*) FROM loans plus any WHERE you used.
• Count the rows. One row per group means the number of rows equals the number of different values (or combinations) in your grouping columns.
• Look for bare columns. Any selected column not in GROUP BY and not inside an aggregate is a bug waiting to be read as fact.
• Spot-check one group. Pick the smallest group and look at its rows with a plain WHERE query to confirm the numbers.

Field notes

• GROUP BY gathers rows with the same value into groups, then summarises each one.
• Grouping by several columns gives one group per combination that actually occurs.
• Select only grouped columns and aggregates, so each result row has one clear value.
• ORDER BY runs after grouping, so it can sort by a count or its alias.

Easy to get wrong

book_title is neither grouped nor summarised. SQLite shows a random one; other databases reject the query.

Without GROUP BY the whole table is one group, so you get one row with a meaningless genre.

WHERE runs before groups exist, so it cannot see a count. Group filters go in HAVING.

An extra grouping column splits groups further. Here, most genres appear twice with no branch shown, which reads as duplicates.

Questions people ask

Is GROUP BY the same as DISTINCT?

SELECT DISTINCT genre and SELECT genre ... GROUP BY genre return the same list. Use DISTINCT to remove duplicates and GROUP BY when you also want counts or totals.

Can I group by a column I do not select?

It runs. The output rows have no label, though, so readers cannot tell the groups apart. Select every grouping column.

Does GROUP BY sort the result?

Sometimes, as a side effect. No database promises it. Add ORDER BY whenever you need a particular order.

Can I group by column number, like GROUP BY 1?

SQLite, PostgreSQL and MySQL accept it, meaning the first selected column. It breaks without any error when someone reorders the columns, so names are safer.