Groups with GROUP BY

How many games of each genre does the shop have? With what you know so far, you'd need a query for each genre: WHERE genre = 'Action', WHERE genre = 'Adventure', and so on, eight times. GROUP BY does it in one query:

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

GROUP BY genre splits the rows into groups: all the rows with the same genre go into one group. The four action games (Shadow Ninja, Star Pilots, Zombie Town and Robot Rebellion) make one group, the three adventure games another, and so on. The aggregate function then works on each group separately, and the result has one row for each group: the genre and the number of its games.

Every aggregate function works this way, and you can use several at once:

SELECT genre, MIN(price) AS cheapest, MAX(price) AS most_expensive
FROM games
GROUP BY genre
ORDER BY genre;

GROUP BY comes after FROM and WHERE, and before ORDER BY. The groups may come sorted, but nothing guarantees it, so when the order matters, add ORDER BY. Rows that have NULL in the column form a group of their own.

What can stand in SELECT next to an aggregate function? The columns you group by, because they're the same in the whole group, and aggregate functions, which compute one value from the group. Nothing else: a group has many rows with different titles, so which one should the result show?

Typical mistake: a column that isn't in GROUP BY. Most databases report an error saying that the column must appear in GROUP BY or be used in an aggregate function. SQLite doesn't; it takes the title from one row of each group:

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

Each row now shows the title of one game next to the number of games of the whole genre, which makes no sense. When you see such a result, check that each column in SELECT is either in GROUP BY or inside an aggregate function.