Grouping with GROUP BY and HAVING

Sorting laundry into piles, then counting each pile: five shirts, three pairs of socks, two towels.

In Aggregates, COUNT, SUM, and the rest squashed a whole table into one row. That answers "how many books are there?" But the question you usually want answered is a bit finer: how many books are there in each genre? How many loans has each member made?

That's GROUP BY: it splits the rows into groups, then runs the aggregate once for every group.

Laundry piles

Picture a pile of clean laundry. Before you can say "I have five shirts," you sort everything into piles: shirts here, socks there, towels over there. Then you count each pile separately.

GROUP BY does both steps. It sorts rows into piles that share the same value, and then the aggregate summarizes each pile, giving one result row per pile.

Your first GROUP BY

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

+---------+-------+
|  genre  | books |
+---------+-------+
| Mystery |     1 |
| Novel   |     3 |
| Science |     2 |
+---------+-------+

Step by step:

1. GROUP BY genre sorts the six books into three piles: Mystery, Novel, and Science.
2. COUNT(*) counts the rows in each pile.
3. You get one row per pile, showing the genre and its count.

Any aggregate works per group:

SELECT
	genre,
	COUNT(*) AS books,
	SUM(copies_owned) AS copies,
	MIN(published_year) AS oldest
FROM books
GROUP BY genre;

+---------+-------+--------+--------+
|  genre  | books | copies | oldest |
+---------+-------+--------+--------+
| Mystery |     1 |      3 |   2002 |
| Novel   |     3 |     12 |   1887 |
| Science |     2 |      4 |   1980 |
+---------+-------+--------+--------+

The golden rule

In a grouped query, every column you SELECT must be one of two things:

• a column you grouped by, like genre, which has the same value for every row in the pile; or
• an aggregate, like COUNT(*), which boils the pile down to one value.

Anything else is ambiguous. In SELECT genre, title FROM books GROUP BY genre, the Novel pile holds three books. Which title should appear on its one row? MySQL and Oracle reject the query outright. SQLite quietly picks a title from somewhere in the pile, which is almost never what you meant. Follow the rule, and your query means the same thing in every database.

Loans per member

Grouping really shines on tables that record events, like loans. Here's how many loans each member has made, most first:

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

+-----------+-------+
| member_id | loans |
+-----------+-------+
|         1 |     2 |
|         2 |     2 |
|         3 |     2 |
|         4 |     1 |
+-----------+-------+

Right now, you see member numbers rather than names. In Combining Tables with JOIN, you'll put the names right next to the counts.

Grouping by several columns

Give GROUP BY more than one column, and it makes a pile for every combination of values. How many loans has each member made, split by whether they've been returned?

SELECT
	member_id,
	returned_date IS NOT NULL AS returned,
	COUNT(*) AS loans
FROM loans
GROUP BY member_id, returned
ORDER BY member_id, returned;

+-----------+----------+-------+
| member_id | returned | loans |
+-----------+----------+-------+
|         1 |        0 |     1 |
|         1 |        1 |     1 |
|         2 |        0 |     2 |
|         3 |        0 |     1 |
|         3 |        1 |     1 |
|         4 |        1 |     1 |
+-----------+----------+-------+

(returned_date IS NOT NULL gives 1 for returned loans and 0 for loans still out, and you can group by it like any other column.) Member 1 has one returned loan and one still out, so they get two rows.

Filtering groups: HAVING

What if you only want the genres with more than one book? You can't use WHERE COUNT(*) > 1, because WHERE filters rows, before any grouping happens, and at that point there are no counts yet.

For filtering groups, after they're counted, SQL has HAVING:

SELECT genre, COUNT(*) AS books
FROM books
GROUP BY genre
HAVING COUNT(*) > 1;

+---------+-------+
|  genre  | books |
+---------+-------+
| Novel   |     3 |
| Science |     2 |
+---------+-------+

The difference in one sentence: WHERE filters rows before grouping; HAVING filters groups after. You can use both in one query:

SELECT member_id, COUNT(*) AS open_loans
FROM loans
WHERE returned_date IS NULL
GROUP BY member_id
HAVING COUNT(*) >= 2;

+-----------+------------+
| member_id | open_loans |
+-----------+------------+
|         2 |          2 |
+-----------+------------+

WHERE first keeps only the loans still out. Then GROUP BY counts them per member, and HAVING keeps only the members with two or more open loans.

The full order of a query

You now know every part of a basic SELECT. They always appear in this order:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

And the database works through them roughly in this order: take the rows from the table, filter them with where, sort them into groups, filter the groups with having, work out the select list, then order and limit the result. That's why an alias made in SELECT works in ORDER BY, which comes later, but not in WHERE, which runs earlier.

Try it

This creates a table of cafeteria orders and runs four grouped queries. Predict each result, row by row.

Running SQL here

Running SQL right in the browser is coming to Syntaxia soon. For now, you can read and edit the queries here. To run them yourself, open the shell with sqlite3, type .mode table, and paste them in.

Check your prediction

+---------+--------+-------+
| student | orders | spent |
+---------+--------+-------+
| Ben     |      3 |   105 |
| Maria   |      3 |   100 |
| Carlo   |      1 |    65 |
+---------+--------+-------+
+-------+-------+
| item  | times |
+-------+-------+
| adobo |     3 |
| juice |     3 |
+-------+-------+
+-----+-------------+
| day | drink_sales |
+-----+-------------+
| Mon |          20 |
| Tue |          20 |
| Wed |          20 |
+-----+-------------+
+---------+-------+-------+
| student | item  | times |
+---------+-------+-------+
| Ben     | juice |     2 |
+---------+-------+-------+

In Query 3, WHERE removes every non-juice order before grouping, so Tuesday's juice sale is the only one counted that day. Query 4 groups by student and item together, so only Ben's two juices make a pile bigger than one.

Try it yourself

1. In your library database, count the members in each grade, sorted by grade.
2. For each book, count how many times it's been borrowed, using book_id. Which book has been borrowed most?
3. Find the genres whose total copies_on_loan is at least 3.

Check your understanding

What does SELECT genre, COUNT(*) FROM books GROUP BY genre; give back?

One row for the whole table

One row per genre, with how many books are in it

One row per book

An error

You want only the groups with more than 5 rows. Which clause do you use?

WHERE COUNT(*) > 5

ORDER BY COUNT(*) > 5

LIMIT 5

HAVING COUNT(*) > 5

In a grouped query, which columns can you safely SELECT?

Any column at all

Only aggregates

Only the columns you grouped by, and aggregates

Only the first column

Up next

Member numbers and book numbers are fine for a computer, but people want names and titles. The information is all there, just spread across three tables. Next, you'll bring them together in Combining Tables with JOIN.