HAVING

Overview

The HAVING clause is used to filter groups after GROUP BY. It's similar to WHERE, but WHERE filters rows before grouping, while HAVING filters groups after aggregation.

HAVING is used with aggregate functions and GROUP BY to filter groups based on aggregate values, which cannot be done with WHERE.

Basic Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_function(column2) condition;

HAVING vs WHERE

Understanding the difference:

SQL Execution Order

Understanding when HAVING is evaluated:

1. FROM (identify tables)
2. WHERE (filter rows)
3. GROUP BY (group rows)
4. HAVING (filter groups)
5. SELECT (select columns)
6. ORDER BY (sort results)

Examples

Example 1: Filter Groups by Count

SELECT 
    category,
    COUNT(*) AS product_count
FROM Products
GROUP BY category
HAVING COUNT(*) > 10;

Example 2: Filter Groups by Sum

SELECT 
    customer_id,
    SUM(total_amount) AS total_spent
FROM Orders
GROUP BY customer_id
HAVING SUM(total_amount) > 1000;

Example 3: HAVING with WHERE

SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS average_price
FROM Products
WHERE price > 50  -- Filter rows before grouping
GROUP BY category
HAVING COUNT(*) > 5  -- Filter groups after grouping
  AND AVG(price) > 100;

Example 4: Multiple HAVING Conditions

SELECT 
    category,
    COUNT(*) AS product_count,
    SUM(stock_quantity) AS total_stock
FROM Products
GROUP BY category
HAVING COUNT(*) > 10
  AND SUM(stock_quantity) > 1000;

Why You Can't Use WHERE for Aggregates

WHERE cannot be used with aggregate functions because WHERE filters rows before aggregation:

-- ❌ ERROR: Cannot use WHERE with aggregate
SELECT category, COUNT(*)
FROM Products
GROUP BY category
WHERE COUNT(*) > 10;  -- Error!

-- ✅ CORRECT: Use HAVING with aggregate
SELECT category, COUNT(*)
FROM Products
GROUP BY category
HAVING COUNT(*) > 10;  -- Works!

HAVING with Different Aggregates

Example 1: HAVING with AVG

SELECT 
    category,
    AVG(price) AS average_price
FROM Products
GROUP BY category
HAVING AVG(price) > 100;

Example 2: HAVING with MIN and MAX

SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM Products
GROUP BY category
HAVING MAX(price) - MIN(price) > 100;

HAVING with Aliases

Some databases allow using aliases in HAVING, while others require the full expression:

-- Using alias in HAVING (works in MySQL)
SELECT 
    category,
    COUNT(*) AS product_count
FROM Products
GROUP BY category
HAVING product_count > 10;

-- Using full expression (works everywhere)
SELECT 
    category,
    COUNT(*) AS product_count
FROM Products
GROUP BY category
HAVING COUNT(*) > 10;

Common Use Cases

• Finding categories with more than X products
• Identifying customers who spent more than a threshold
• Filtering groups based on aggregate values
• Creating reports with group-level conditions
• Analyzing data by groups with specific criteria

Best Practices

• Use WHERE first: Filter rows before grouping when possible
• Use HAVING for groups: Filter groups after aggregation
• Use full expressions: For portability, avoid aliases in HAVING
• Combine both: Use WHERE and HAVING together when appropriate
• Test logic: Verify HAVING conditions produce expected results

Common Mistakes

• Using WHERE with aggregates: Must use HAVING for aggregate conditions
• Forgetting GROUP BY: HAVING requires GROUP BY (except with window functions)
• Using column aliases: May not work in all databases
• Confusing WHERE and HAVING: Understand execution order

Next Steps

Learn about checking for existence with EXISTS, or explore conditional logic with CASE.