HAVING Clause
HAVING filters groups after GROUP BY - just as WHERE filters rows before grouping. Use HAVING when your filter condition involves an aggregate function. You cannot use aggregate functions in WHERE, only in HAVING.
WHERE vs HAVING
- WHERE - filters individual rows BEFORE grouping
- HAVING - filters groups AFTER grouping
- WHERE cannot use aggregate functions (COUNT, SUM, etc.)
- HAVING can reference aggregate functions and GROUP BY columns
- Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
- Can use both WHERE and HAVING in the same query
HAVING Examples
-- Users who placed more than 1 order
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;
-- Result: user_id=1 (2 orders)
-- Payment methods used more than once
SELECT payment_method, COUNT(*) AS uses
FROM payments
GROUP BY payment_method
HAVING COUNT(*) >= 1;
-- Users who spent more than $500 total
SELECT
u.name,
SUM(o.total_amount) AS total_spent
FROM orders o
JOIN users u ON o.user_id = u.id
GROUP BY u.id, u.name
HAVING SUM(o.total_amount) > 500;
-- WHERE (row filter) + HAVING (group filter) together
SELECT
user_id,
COUNT(*) AS order_count,
SUM(total_amount) AS revenue
FROM orders
WHERE status != 'cancelled' -- filter rows first
GROUP BY user_id
HAVING SUM(total_amount) > 200 -- then filter groups
ORDER BY revenue DESC;
-- WRONG: aggregate in WHERE (syntax error)
-- SELECT user_id FROM orders WHERE COUNT(*) > 1; -- ERROR!
-- CORRECT: use HAVING after GROUP BYQuick Quiz
Tip
Tip
Practice HAVING Clause in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
GROUP BY collapses rows by a column - aggregates summarize each group
Common Mistake
Warning
A common mistake with HAVING Clause is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of HAVING Clause from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- HAVING filters groups after GROUP BY - just as WHERE filters rows before grouping.
- WHERE - filters individual rows BEFORE grouping
- HAVING - filters groups AFTER grouping
- WHERE cannot use aggregate functions (COUNT, SUM, etc.)