CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 4 of 5 · 1 min read

Filtering groups with HAVING

Why a condition on an aggregate cannot go in WHERE.

Filtering groups with HAVING

Think of totaling every cashier's receipts, then afterward keeping only the cashiers whose total exceeded a certain amount. The filtering happens after the totaling, not before it.

In plain words: WHERE filters individual rows before any grouping happens. HAVING filters groups after grouping and aggregation have already happened. A condition on an aggregate value, such as "only customers with more than 5 orders," must go in HAVING, since COUNT does not exist yet at the point WHERE is applied.

A worked example. Customers who have placed more than 5 orders.

The key lines.

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

HAVING

HAVING filters groups after GROUP BY and aggregation, based on a condition that can reference an aggregate function. WHERE, by contrast, filters individual rows before grouping happens.

Example: HAVING COUNT(*) > 5 keeps only customer groups with more than five orders; the same condition cannot go in WHERE, since COUNT(*) is not yet computed at that stage.

Remember this

WHERE runs before grouping and cannot see an aggregate's result. HAVING runs after grouping and can. A condition that mentions COUNT, SUM, AVG, MIN or MAX belongs in HAVING.

Quick notes

  • COUNT, SUM, AVG, MIN and MAX each compute one value from many rows.
  • Without GROUP BY, an aggregate computes over the entire table.
  • GROUP BY splits rows into groups; aggregates then compute once per group.
  • Every non-aggregated SELECT column must appear in GROUP BY.
  • WHERE filters rows before grouping; HAVING filters groups after grouping, and can reference an aggregate.