Lesson · 12 min

Group rows

A result for each group

An aggregate function over the whole table gives one row. GROUP BY splits the rows into groups first, and then the function runs once for each group.

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

The database puts all rows with the same genre in one group. The result has one row for each genre.

A good rule: every column in the SELECT list must be either in the GROUP BY clause or inside an aggregate function.

Filtering groups with HAVING

WHERE removes rows before the groups are made. HAVING removes whole groups after the groups are made. Use HAVING when the condition uses an aggregate function.

SELECT genre, COUNT(*) AS book_count
FROM books
GROUP BY genre
HAVING COUNT(*) >= 2;

The order of clauses

The full order is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. You can leave out the clauses that you do not need, but the ones you write must be in this order.

Your turn

Write one query for each task, one after the other. You can write a query on several lines. The semicolon marks the end.

Tasks

5 to do

Task 1, not checked yet. For each genre, count the books. Select the genre and the count, and name the count book_count.
Task 2, not checked yet. For each author, count the books. Name the count book_count.
Task 3, not checked yet. For each genre, find the average price, rounded to 2 decimals. Name it average_price.
Task 4, not checked yet. Select the authors that have more than one book, with the number of their books as book_count. Use HAVING.
Task 5, not checked yet. For each genre, find the total price, rounded to 2 decimals, and name it total_price. Put the genre with the biggest total first.

Task 1 of 5

For each genre, count the books. Select the genre and the count, and name the count book_count.