Filtering Groups with HAVING

Which genres have at least 3 games? WHERE filters rows, so you might try this:

SELECT genre, COUNT(*) AS game_count
FROM games
WHERE COUNT(*) >= 3
GROUP BY genre;

Error on line 1: misuse of aggregate: COUNT()

The database complains that COUNT is somewhere it doesn't belong. WHERE checks the rows one by one before they're grouped, and at that point, there's nothing to count yet. To filter groups, SQL has HAVING. It comes right after GROUP BY, and its condition is checked for each group once the aggregate functions are computed:

SELECT genre, COUNT(*) AS game_count
FROM games
GROUP BY genre
HAVING COUNT(*) >= 3
ORDER BY genre;

The groups that don't meet the condition are left out of the result. In HAVING, you can use everything you know from WHERE: comparisons, AND, OR, NOT, BETWEEN… and aggregate functions, even ones that aren't in SELECT. These are the genres whose games cost $25 or less on average:

SELECT genre
FROM games
GROUP BY genre
HAVING AVG(price) <= 25
ORDER BY genre;

A query's parts are written in this order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

Typical mistake: WHERE instead of HAVING, as in the first query. Whenever a condition has an aggregate function in it, it belongs in HAVING.