Lesson Beginner

HAVING

WHERE filters rows before aggregation.
HAVING filters groups after aggregation.
That is the entire distinction, and also why HAVING exists as its own clause.

WHERE vs HAVING #

Put the predicate where it makes sense in the pipeline:

You want to filter...	Use
Raw rows before grouping (e.g. `status = 'paid'`)	`WHERE`
Aggregated groups after grouping (e.g. `COUNT(*) > 1`)	`HAVING`

WHERE cannot reference aggregates, because they haven't been computed yet. HAVING can reference them freely.

sql

-- Users with more than one order
SELECT
  user_id,
  COUNT(*) AS orders
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;

Prefer WHERE When You Can #

Tip

If the filter doesn't need an aggregate, use WHERE.
WHERE runs before GROUP BY, so fewer rows reach the aggregate step, which makes the query cheaper. HAVING is for predicates that inspect the group itself (counts, sums, ratios). Using HAVING for row-level conditions works but wastes work.

sql

-- Both count paid orders per user and return the same rows,
-- but WHERE filters earlier
SELECT user_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

-- Slower: HAVING can filter status, but only after aggregating everything
SELECT user_id, COUNT(*) AS paid_orders
FROM orders
GROUP BY user_id, status
HAVING status = 'paid';

Aggregates in HAVING Don't Need to Be in SELECT #

You can filter on an aggregate you don't return:

sql

-- Products with at least one order over $100,
-- but only return the product_id and row count
SELECT
  product_id,
  COUNT(*) AS orders
FROM orders
GROUP BY product_id
HAVING MAX(total) > 100;

Try It #

Find users who have placed more than one order.

Loading SQL editor...

Practice #

For paid orders only, return product_id and product_revenue (sum of total, rounded to 2). Include only products whose paid revenue is strictly greater than 100. No ordering requirement.

Loading SQL editor...

Mistakes to Watch For #

Putting row-level conditions in HAVING when WHERE would work. It's slower and harder to read.
Trying to put aggregates in WHERE. SQL rejects it because aggregates don't exist yet at WHERE time.
Forgetting that HAVING operates on groups: HAVING status = 'paid' only makes sense when status is a grouping key.
Assuming every dialect lets you reuse a SELECT alias in HAVING. The safe pattern is to repeat the aggregate expression.

Knowledge check #

3 questions

0 / 3 answered

WHERE COUNT(*) > 5 is:
HAVING status = 'paid' (status is a non-aggregated column):
You want regions where total revenue > $10K and only paid orders. The right query shape:

Next Step #

Continue to CASE & Conditional Aggregation — count and sum different things in one pass by putting conditions inside the aggregate.