You write a query in one order. PostgreSQL runs its clauses in another:
FROM
WHERErows
GROUP BY
HAVINGgroups
SELECT
ORDER BY
LIMIT
WHERE first, HAVING later
- WHERE looks at single rows. It removes rows before the groups are made.
- HAVING looks at groups. It removes groups after the aggregates are calculated.
The countries with 2 or more member customers need both:
sql
SELECT country, COUNT(*)
FROM customers
WHERE is_member = true
GROUP BY country
HAVING COUNT(*) >= 2;customers
| id | country | is_member |
|---|---|---|
| 1 | CA | true |
| 2 | CA | false |
| 8 | CA | true |
| 3 | IT | true |
| 5 | US | true |
| 4 | US | false |
A condition on one row's value goes in WHERE. It also makes the query faster, because fewer rows go into the groups. Use HAVING only for conditions on aggregates.
Aliases come from SELECT
SELECT runs after WHERE, GROUP BY and HAVING. An alias that SELECT makes does not exist yet in those clauses:
text
SELECT country, COUNT(*) AS n FROM customers GROUP BY country HAVING n > 1;
ERROR: column "n" does not existRepeat the expression: HAVING COUNT(*) > 1. ORDER BY runs after SELECT, so ORDER BY n DESC works.