WHERE and HAVING Together

A query can have both WHERE and HAVING, and each of them has its own job. WHERE filters rows before they're grouped: the rows that don't meet its condition don't get into any group. HAVING filters groups once they're made and counted.

Which genres have at least 3 games that are already out? Whether a game is out is a question about one row, so it goes in WHERE. “At least 3” is about a whole group, so it goes in HAVING:

SELECT genre, COUNT(*) AS released_games
FROM games
WHERE released IS NOT NULL
GROUP BY genre
HAVING COUNT(*) >= 3
ORDER BY genre;

In the previous exercise, Adventure had 3 games too. But one of them, Mystery Manor, isn't out yet, and WHERE leaves it out before the counting. Adventure then has only 2 games, and HAVING drops it. Action had 4 games, and now it counts only the 3 that are out.

How do you know where a condition belongs? Ask whether you can decide it by looking at one row. “Out already”, “paid”, “from London”: one row is enough, so WHERE. “At least 3 games”, “more than $60 in total”, “4 stars on average”: you need the whole group, so HAVING. A condition on the column you group by, such as genre = 'Action', would work in both, but put it in WHERE: the database then doesn't group rows that it would throw away anyway.

Typical mistake: a condition about rows in HAVING. Other databases report an error, because the column isn't in GROUP BY. SQLite doesn't: it checks the release date of one game of each group and keeps or drops the whole group by it. Here, it keeps all the genres, with all their games:

SELECT genre, COUNT(*) AS released_games
FROM games
GROUP BY genre
HAVING released IS NOT NULL
ORDER BY genre;

No error, just a wrong result: Action has 4 games again, including Robot Rebellion, which isn't out yet.