GROUP BY and HAVING

GROUP BY splits rows into groups that share a value and calculates for each group separately. “How many students come from each city?”, “what is the average price in each category?” — that is GROUP BY.

How it works

SELECT city, COUNT(*) AS students
FROM students
GROUP BY city
ORDER BY students DESC;

The database sorts the students into “piles” by city and then counts each pile. Each city becomes one row of the result.

The rule

Every column in SELECT must either appear in GROUP BY or sit inside an aggregate:

SELECT course, funding, COUNT(*) FROM students GROUP BY course, funding ORDER BY course, funding;

PostgreSQL always enforces this rule: break it and you get “must appear in the GROUP BY clause”.

HAVING — a condition on groups

WHERE filters rows before grouping; HAVING filters groups after:

SELECT city, COUNT(*) AS students
FROM students
GROUP BY city
HAVING COUNT(*) > 25
ORDER BY students DESC;

Both together:

SELECT city, COUNT(*) AS grant_students
FROM students
WHERE funding = 'grant'
GROUP BY city
HAVING COUNT(*) >= 10;

The order of execution

A query is written in this order, and the database runs it in nearly the same order:

1. FROM — which table;
2. WHERE — which rows;
3. GROUP BY — grouping;
4. HAVING — which groups;
5. SELECT — what to show;
6. ORDER BY, LIMIT — order and limit.

That is why COUNT(*) cannot be used in WHERE — at that step there are no groups yet.

Remember

• GROUP BY calculates per group; plain columns in SELECT must be in GROUP BY.
• WHERE is for rows, HAVING is for groups.
• Order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.