sqlstreak

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(*) > 3 in 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.

Practise it now