Groups by Several Columns

GROUP BY can have several columns, separated by commas. Rows then get into the same group only when they have the same values in all of them. How many games of each genre are there for each age?

SELECT genre, age_rating, COUNT(*) AS game_count
FROM games
GROUP BY genre, age_rating
ORDER BY genre, age_rating;

The four action games have split up: two are for 16 and older (Shadow Ninja and Robot Rebellion), one for 12 and one for 18. The result has one row for each combination of a genre and an age that the table has. Combinations without any game, such as action games for 3-year-olds, aren't there.

The rule from the previous exercises still holds: each column in SELECT that isn't inside an aggregate function must be in GROUP BY. Here, that's both genre and age_rating.

You can also group by an expression, for example by a function. With strftime('%Y', …) from lesson 4, you get the number of customers who joined in each year:

SELECT strftime('%Y', joined) AS year, COUNT(*) AS new_customers
FROM customers
GROUP BY strftime('%Y', joined)
ORDER BY year;

In GROUP BY, write the same expression as in SELECT. And you can combine it with columns, too: GROUP BY strftime('%Y', joined), city would count the new customers of each city in each year.

Typical mistake: leaving a column out of GROUP BY. SELECT genre, age_rating, COUNT(*) FROM games GROUP BY genre; has one row for each genre, and its age_rating is that of one game of the genre, as you saw with the titles two exercises ago.