The HAVING Clause

What is HAVING?

HAVING filters groups produced by GROUP BY — it's the equivalent of WHERE, but for aggregated results. You use it when you want to keep only the groups that meet a certain condition based on an aggregate value.

-- Only show drink types with more than $500 in total revenue
SELECT drink_type, SUM(price_usd) AS total_revenue
FROM coffee_shop_sales
GROUP BY drink_type
HAVING SUM(price_usd) > 500;

Without HAVING, you'd get every drink type. With it, only the groups where total revenue exceeds $500 are returned.

HAVING vs WHERE

This is the most important distinction to understand. WHERE filters individual rows before grouping. HAVING filters groups after aggregation. You can't use WHERE with an aggregate function — that's what HAVING is for.

-- WRONG: WHERE cannot reference an aggregate
SELECT drink_type, SUM(price_usd)
FROM coffee_shop_sales
GROUP BY drink_type
WHERE SUM(price_usd) > 500;  -- error!

-- CORRECT: HAVING filters on the aggregated result
SELECT drink_type, SUM(price_usd) AS total_revenue
FROM coffee_shop_sales
GROUP BY drink_type
HAVING SUM(price_usd) > 500;

Using WHERE and HAVING together

You can use both in the same query. WHERE narrows down which rows go into the groups, and HAVING then decides which of those groups to keep.

-- Among Large drinks only, show drink types with more than 10 sales
SELECT drink_type, COUNT(*) AS large_sale_count
FROM coffee_shop_sales
WHERE size = 'Large'
GROUP BY drink_type
HAVING COUNT(*) > 10;

WHERE size = 'Large' runs first and removes all non-Large rows. Then GROUP BY groups the remaining rows by drink type. Finally HAVING removes any drink types with 10 or fewer Large sales.

Multiple conditions in HAVING

Just like WHERE, you can combine multiple conditions in HAVING using AND or OR.

-- Car models rented more than 3 times AND with avg rate above $50
SELECT car_model, COUNT(*) AS rental_count, AVG(daily_rate_usd) AS avg_rate
FROM car_rental_records
GROUP BY car_model
HAVING COUNT(*) > 3 AND AVG(daily_rate_usd) > 50;

Common mistakes

Using WHERE instead of HAVING for aggregates — WHERE SUM(...) > 500 is always an error. Aggregate filters must use HAVING.

Using HAVING without GROUP BY — HAVING is almost always paired with GROUP BY. Using it alone is technically allowed in some databases but rarely useful.

Filtering on a column alias — you can't write HAVING total_revenue > 500 in most databases even if you aliased the column. You must repeat the full expression: HAVING SUM(price_usd) > 500.