SQL Practice 6/13

Filter groups with HAVING

WHERE filters rows before grouping - it can't see COUNT(*) or any other aggregate, because those don't exist yet at that point in the query. HAVING runs after GROUP BY, so it can filter on the aggregate result: "only groups where the count is over 1", "only countries with an average age above 30", and so on.

Same idea as WHERE, different stage of the query. If the condition mentions a plain column, WHERE usually works; if it mentions an aggregate like COUNT(*) or SUM(...), you need HAVING.

Same six-row users table again.

Your task: return only the countries with more than one user, along with the count, as total.

You'll practice:

Filtering grouped results with HAVING
Using an aggregate inside a HAVING condition

-- Return only countries with more than one user, with the count as `total`.
SELECT country, COUNT(*) AS total FROM users GROUP BY country;