HAVING clause

HAVING filters groups after aggregation—unlike WHERE, which filters rows before grouping. Use it for conditions on COUNT, SUM, AVG, etc.

HAVING basics

SELECT customer_id,
       SUM(total) AS spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 100;

Practice: Seed customers and orders sample data, then run each query. Verify row counts manually.

WHERE + HAVING together

SELECT customer_id,
       COUNT(*) AS recent_orders,
       SUM(total) AS recent_spent
FROM orders
WHERE ordered_at >= '2025-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 2;

WHERE shrinks rows first; HAVING filters grouped results—order matters for readability and performance.

Common mistake

-- Invalid in standard SQL: aggregate in WHERE
-- SELECT customer_id FROM orders WHERE SUM(total) > 100;

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 100;

Repeat the aggregate expression in HAVING or use a subquery/CTE alias pattern in advanced lessons.

Important interview questions and answers

1. Q: Can WHERE use SUM()?
   A: Not on grouped aggregates—use HAVING or a subquery.
2. Q: HAVING without GROUP BY?
   A: Valid when treating whole table as one group—rare in practice.

Self-check

1. Filter groups with total spent over 100—which clause?
2. Which runs first: WHERE or GROUP BY?

Tip: Filters on SUM/COUNT go in HAVING; filters on raw rows go in WHERE.