Filtering Groups with HAVING

Now that you've mastered GROUP BY, it's time to control which groups make it into your results. The marketplace schema makes this easy to visualize: maybe you want to see only the categories that have at least four products, or order statuses where the average order total is above 50. The HAVING clause lets you filter groups after aggregation, complementing the WHERE clause that filters individual rows before grouping.

Learning Goals

• Know when to use WHERE vs HAVING
• Filter grouped data using counts, averages, and sums
• Combine row-level and group-level filters in the same query
• Avoid common mistakes that lead to confusing results

Understanding HAVING

HAVING evaluates conditions on aggregated groups. A typical pattern looks like this:

Basic HAVING example

SELECT cat.category_name,
       AVG(p.price) AS avg_price
FROM product AS p
JOIN category AS cat ON cat.category_id = p.category_id
GROUP BY cat.category_name
HAVING AVG(p.price) >= 40;

This query shows only categories whose products average 40 or more.

WHERE vs HAVING

Remember the execution order: WHERE filters rows -> GROUP BY creates groups -> HAVING filters groups -> ORDER BY sorts the final output.

WHERE filters rows first

-- Products priced above 30, grouped by category
SELECT cat.category_name,
       COUNT(*) AS product_count
FROM product AS p
JOIN category AS cat ON cat.category_id = p.category_id
WHERE p.price > 30
GROUP BY cat.category_name;

HAVING filters groups after aggregation

-- Categories that have more than 3 products above 30
SELECT cat.category_name,
       COUNT(*) AS product_count
FROM product AS p
JOIN category AS cat ON cat.category_id = p.category_id
WHERE p.price > 30
GROUP BY cat.category_name
HAVING COUNT(*) > 3;

Practical HAVING Examples

Filter by number of rows in each group

Categories with at least 4 products

SELECT cat.category_name,
       COUNT(*) AS total_products
FROM product AS p
JOIN category AS cat ON cat.category_id = p.category_id
GROUP BY cat.category_name
HAVING COUNT(*) >= 4;

Filter by aggregate averages

Statuses with high average order totals

SELECT o.status,
       AVG(o.order_total) AS avg_order_total
FROM orders AS o
GROUP BY o.status
HAVING AVG(o.order_total) >= 50;

Combine multiple aggregate conditions

Busy categories for each status

SELECT cat.category_name,
       o.status,
       COUNT(*) AS items_sold,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_item AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN product AS p ON p.product_id = oi.product_id
JOIN category AS cat ON cat.category_id = p.category_id
GROUP BY cat.category_name, o.status
HAVING COUNT(*) >= 3
   AND SUM(oi.quantity * oi.unit_price) >= 100;

Combining WHERE and HAVING

Strong queries filter out unnecessary rows early with WHERE, then use HAVING for final group-level checks.

Delivered orders in March 2025 by category

SELECT cat.category_name,
       COUNT(*) AS delivered_items,
       COUNT(DISTINCT o.customer_id) AS unique_customers
FROM order_item AS oi
JOIN orders AS o ON o.order_id = oi.order_id
JOIN product AS p ON p.product_id = oi.product_id
JOIN category AS cat ON cat.category_id = p.category_id
WHERE o.order_date LIKE '2025-03%'
  AND o.status = 'Delivered'
GROUP BY cat.category_name
HAVING COUNT(*) >= 2;

Common Pitfalls

• Aggregates in WHERE: Conditions like WHERE COUNT(*) > 3 fail because WHERE runs before grouping.
• Missing GROUP BY: HAVING expects grouped data. Without GROUP BY, the entire dataset is treated as one group.
• Order matters: The correct order is WHERE, GROUP BY, HAVING, then ORDER BY.
• Too few row filters: Use WHERE to shrink the data before grouping; it keeps HAVING efficient.
• Alias confusion: Some engines don't let you reference SELECT aliases inside HAVING; repeat the expression if needed.

Summary

• Use WHERE for individual rows, HAVING for aggregated groups.
• Combine both clauses to answer nuanced questions about the marketplace data.
• HAVING shines when you need thresholds on counts, averages, or sums after grouping.

Quiz

Show quiz

1. Which clause filters rows before grouping occurs?
   A) GROUP BY
   B) WHERE
   C) HAVING
   D) ORDER BY

2. How would you list categories that have at least three products priced above 30?
   A) WHERE COUNT(*) >= 3
   B) HAVING COUNT(*) >= 3
   C) HAVING price >= 30
   D) WHERE price >= 30

3. What does HAVING AVG(o.order_total) > 50 evaluate?
   A) Each individual order row
   B) Each group of rows created by GROUP BY
   C) Only orders with totals greater than 50
   D) It causes an error because AVG must be in SELECT

4. True or False: You can use both WHERE and HAVING in the same query.
   A) True
   B) False

---

Answers

1. B
2. B
3. B
4. A