12 — HAVING

One line: HAVING filters groups after aggregation — it's a WHERE for aggregate results (SUM, COUNT, AVG…).

The idea in plain English

WHERE filters individual rows. But what if your condition is about an aggregate — "customers who spent more than $100 in total"? That total doesn't exist until after grouping, so WHERE can't see it.

HAVING is the answer: it runs after GROUP BY and lets you filter on the aggregated result.

WHERE = filter rows before grouping · HAVING = filter groups after grouping

Why you can't just use WHERE (execution order)

Recall the order SQL actually runs clauses:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

WHERE happens at step 2, before GROUP BY at step 3. So when WHERE runs, SUM(amount) hasn't been computed yet — you literally can't filter on it:

-- ❌ ERROR: the sum doesn't exist when WHERE runs
SELECT customer_id, SUM(amount)
FROM payment
WHERE SUM(amount) > 100     -- aggregate not allowed in WHERE
GROUP BY customer_id;

HAVING sits at step 4, after GROUP BY, so by then the sums exist and you can filter on them. This is the entire reason HAVING exists.

Syntax

SELECT   category_col, AGG(data_col)
FROM     table
WHERE    row_condition        -- optional: filter rows first
GROUP BY category_col
HAVING   AGG(data_col) condition   -- filter groups after aggregating
ORDER BY AGG(data_col);        -- optional

Like ORDER BY, HAVING needs the full aggregate function, not the bare column:

HAVING SUM(amount) > 100     -- ✅
HAVING amount > 100          -- ❌ (that column no longer exists post-group)

Examples (DVD Rental)

-- Customers whose TOTAL spend exceeds $100
SELECT customer_id, SUM(amount)
FROM   payment
GROUP BY customer_id
HAVING SUM(amount) > 100;

-- Stores with more than 300 customers
SELECT store_id, COUNT(*)
FROM   customer
GROUP BY store_id
HAVING COUNT(*) > 300;

WHERE and HAVING together — filter rows first, then filter groups:

-- Among customers (excluding a few IDs), those who spent over $100
SELECT customer_id, SUM(amount)
FROM   payment
WHERE  customer_id NOT IN (184, 87, 477)   -- 1. drop these rows first
GROUP BY customer_id
HAVING SUM(amount) > 100;                   -- 2. then keep big spenders

COUNT(customer_id) and COUNT(*) behave the same here — pick whichever reads clearer. COUNT(customer_id) documents intent ("count customers per store"); COUNT(*) is terser.

Production notes (important)

• Filter as early as possible — prefer WHERE over HAVING when you can. ⚠️ WHERE cuts rows before aggregation, so the database groups less data → faster. A row-level condition dumped into HAVING (e.g. HAVING store_id = 1) still works if the column is grouped, but it filters after doing all the grouping work — wasteful. Rule: row conditions → WHERE; aggregate conditions → HAVING.
• You can't use a SELECT alias in HAVING. Since HAVING (step 4) runs before SELECT (step 5), the alias doesn't exist yet — you must repeat the aggregate:

  SELECT customer_id, SUM(amount) AS total ... GROUP BY customer_id
  HAVING SUM(amount) > 100;   -- ✅ repeat it; HAVING total > 100 fails in standard SQL
  

• HAVING can filter on an aggregate that isn't in the SELECT. You don't have to display the sum to filter by it:

  SELECT customer_id FROM payment
  GROUP BY customer_id HAVING SUM(amount) > 100;   -- valid; sum not shown
  

• HAVING without GROUP BY treats the whole table as one group — occasionally handy (SELECT SUM(amount) FROM payment HAVING SUM(amount) > 1000), but uncommon.

Challenges

All use the DVD Rental database.

Challenge 1 — Warm-up (HAVING + COUNT)

Which ratings have more than 200 films? Return rating and the film count. (Table: film.)

SELECT rating, COUNT(*)
FROM   film
GROUP BY rating
HAVING COUNT(*) > 200;

Challenge 2 — WHERE + HAVING together

Considering only payments over $2, find the customers who made more than 20 such payments. Show customer_id and that count, most first. (Table: payment.)

SELECT customer_id, COUNT(*) AS big_payments
FROM   payment
WHERE  amount > 2                 -- row filter: keep only >$2 payments
GROUP BY customer_id
HAVING COUNT(*) > 20              -- group filter: 20+ of them
ORDER BY COUNT(*) DESC;

Challenge 3 — Spot the bug 🔎 (WHERE vs HAVING)

A teammate wants "customers who spent more than $150 total" and writes:

SELECT customer_id, SUM(amount)
FROM   payment
WHERE  SUM(amount) > 150
GROUP BY customer_id;

It errors. Why, and what's the fix? Bonus: where would a customer_id NOT IN (…) filter go?

SELECT customer_id, SUM(amount)
FROM   payment
GROUP BY customer_id
HAVING SUM(amount) > 150;

... WHERE customer_id NOT IN (184, 87)
GROUP BY customer_id
HAVING SUM(amount) > 150;

30-second recap