SQL GROUP BY and HAVING
Aggregate functions like COUNT, SUM and AVG squash many rows into one number. GROUP BY runs them once per group instead, so one query can answer “how many of each?”.
Aggregates on their own
Without GROUP BY, an aggregate collapses the whole table into one row. COUNT(*) counts rows; COUNT(column) counts only rows where that column isn't NULL.
SELECT COUNT(*) AS pets, COUNT(breed) AS with_breed, AVG(age) AS avg_age FROM pets;
One row per group
GROUP BY species splits the rows into buckets, one per species, and the aggregates run per bucket. Every column in SELECT must either be in the GROUP BY or inside an aggregate.
SELECT species, COUNT(*) AS pets, AVG(weight_kg) AS avg_weight FROM pets GROUP BY species ORDER BY pets DESC;
HAVING filters groups
WHERE runs before grouping and can't see aggregates. HAVING runs after, so it can keep only the groups you care about. You can use both in one query.
SELECT species, COUNT(*) AS waiting FROM pets WHERE adopted = false GROUP BY species HAVING COUNT(*) >= 2;
Grouping joined data
The most useful reports group across a join. Here, revenue per city at a pizza shop: join customers to orders to items, filter, then group.
SELECT c.city, SUM(oi.quantity * m.price) AS revenue FROM customers c JOIN orders o ON o.customer_id = c.id JOIN order_items oi ON oi.order_id = o.id JOIN menu_items m ON m.id = oi.item_id WHERE o.status = 'delivered' GROUP BY c.city ORDER BY revenue DESC;
Common mistakes
Selecting a column that isn't grouped or aggregated. PostgreSQL raises “must appear in the GROUP BY clause”; SQLite quietly returns an arbitrary value, which hides bugs.
Putting
COUNT(*) > 3in WHERE instead of HAVING.Using
COUNT(*)after a LEFT JOIN when you meant to count matches. Count a column from the right table instead.
Questions
Can I GROUP BY a column alias?+
In PostgreSQL and SQLite, yes: GROUP BY month works if month is a SELECT alias. Some databases don't allow it, so repeating the expression is the most portable choice.
Can I GROUP BY more than one column?+
Yes. GROUP BY species, adopted gives one row per combination.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.