WHERE keeps rows. To keep groups, use HAVING. It comes after GROUP BY:
sql
SELECT country, COUNT(*)
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;groups
| country | count |
|---|---|
| CA | 3 |
| US | 2 |
| IT | 1 |
| BR | 1 |
| … | … |
SELECT country, COUNT(*)columns and aggregatesFROM customerstableGROUP BY countrymake groupsHAVING COUNT(*) > 1keep these groups
The HAVING condition usually uses an aggregate: HAVING SUM(stock) >= 100, HAVING AVG(price) < 20. It can use the AND, OR and NOT of a WHERE condition.
An aggregate cannot go in WHERE
WHERE looks at one row at a time, before the groups exist. An aggregate in WHERE fails:
text
SELECT country FROM customers WHERE COUNT(*) > 1 GROUP BY country;
ERROR: aggregate functions are not allowed in WHEREA condition on an aggregate goes in HAVING.