Filtering Groups with HAVING

You've learned how WHERE filters individual rows and how GROUP BY creates groups. But what if you want to filter based on aggregate results? For example: "Show me only majors with an average GPA above 3.5" or "Find regions where total sales exceed $1 million." You can't use WHERE for this because WHERE happens before grouping! That's where HAVING comes in—it filters groups after aggregation.

📚 What You'll Learn

• What HAVING does and why it's essential
• The critical difference between WHERE and HAVING
• When to use WHERE vs HAVING (and when to use both!)
• How HAVING filters groups, not rows
• Using aggregate functions in HAVING conditions
• Combining WHERE and HAVING in the same query
• Multiple conditions in HAVING (AND, OR)
• The execution order: WHERE → GROUP BY → HAVING
• Common beginner mistakes and how to avoid them
• Real-world business analytics scenarios
• Hands-on practice exercises

1. What Does HAVING Do?

HAVING filters groups based on aggregate results. It answers questions like "Which groups meet this aggregate condition?"

Basic Syntax

SELECT column, AGGREGATE_FUNCTION(column)
FROM table_name
GROUP BY column
HAVING AGGREGATE_FUNCTION(column) condition;

Simple Example

-- Show only majors with average GPA above 3.5
SELECT 
    major,
    AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5;

2. WHERE vs HAVING - The Critical Difference

This is the most important concept to understand:

3. Visual Example: WHERE vs HAVING in Action

Scenario: Student GPA Analysis

SELECT major, AVG(gpa) AS avg_gpa
FROM students
WHERE gpa > 3.0  -- Filter individual students FIRST
GROUP BY major;

SELECT major, AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5;  -- Filter groups AFTER calculating average

4. Common HAVING Conditions

COUNT: Filter by Group Size

-- Show majors with more than 50 students
SELECT major, COUNT(*) AS student_count
FROM students
GROUP BY major
HAVING COUNT(*) > 50;

SUM: Filter by Total

-- Show regions with total sales over $1 million
SELECT region, SUM(sales) AS total_sales
FROM orders
GROUP BY region
HAVING SUM(sales) > 1000000;

AVG: Filter by Average

-- Show products with average rating above 4.5
SELECT product_name, AVG(rating) AS avg_rating
FROM reviews
GROUP BY product_name
HAVING AVG(rating) > 4.5;

MIN/MAX: Filter by Extremes

-- Show categories where the cheapest product costs over $100
SELECT category, MIN(price) AS lowest_price
FROM products
GROUP BY category
HAVING MIN(price) > 100;

5. The Power Combo: WHERE + HAVING

You can (and often should) use WHERE and HAVING together! They work at different stages:

-- Find high-performing majors, but only look at recent students
SELECT 
    major,
    AVG(gpa) AS avg_gpa,
    COUNT(*) AS student_count
FROM students
WHERE year >= 2023                -- 1. Filter rows: only recent students
GROUP BY major                    -- 2. Group by major
HAVING AVG(gpa) > 3.5             -- 3. Filter groups: only high averages
   AND COUNT(*) >= 10;            -- 4. Filter groups: only substantial size

Execution Flow:

6. Multiple Conditions in HAVING

Just like WHERE, you can use AND, OR in HAVING:

Using AND (Both Conditions Must Be True)

-- Majors with high average AND large enrollment
SELECT major, AVG(gpa) AS avg_gpa, COUNT(*) AS count
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5 AND COUNT(*) > 30;

Using OR (Either Condition Can Be True)

-- Majors that are either high-performing OR very popular
SELECT major, AVG(gpa) AS avg_gpa, COUNT(*) AS count
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.8 OR COUNT(*) > 100;

Complex Conditions with Parentheses

-- High GPA with good size, OR very large regardless of GPA
SELECT major, AVG(gpa) AS avg_gpa, COUNT(*) AS count
FROM students
GROUP BY major
HAVING (AVG(gpa) > 3.5 AND COUNT(*) > 20) 
    OR COUNT(*) > 100;

7. Can HAVING Filter Non-Aggregate Columns?

Yes! HAVING can filter on columns that appear in GROUP BY:

-- These both work, but WHERE is more efficient
SELECT major, AVG(gpa)
FROM students
GROUP BY major
HAVING major LIKE 'C%';  -- Filter groups starting with 'C'

-- Better approach: Use WHERE
SELECT major, AVG(gpa)
FROM students
WHERE major LIKE 'C%'     -- Filter rows earlier (more efficient)
GROUP BY major;

8. Common Mistakes with HAVING

Mistake #1: Using Aggregate in WHERE

-- ❌ WRONG: Can't use aggregates in WHERE
SELECT major, AVG(gpa)
FROM students
WHERE AVG(gpa) > 3.5
GROUP BY major;

-- ✅ CORRECT: Use HAVING for aggregates
SELECT major, AVG(gpa)
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5;

Mistake #2: Using HAVING Without GROUP BY

-- ❌ WRONG: HAVING requires GROUP BY
SELECT AVG(gpa)
FROM students
HAVING AVG(gpa) > 3.5;

-- ✅ CORRECT: Use WHERE or add GROUP BY
SELECT AVG(gpa)
FROM students
-- No HAVING needed; just calculate the aggregate

Mistake #3: Filtering Rows with HAVING Instead of WHERE

-- ❌ INEFFICIENT: HAVING on non-aggregate
SELECT major, AVG(gpa)
FROM students
GROUP BY major
HAVING major = 'Computer Science';

-- ✅ BETTER: Use WHERE (filters before grouping)
SELECT major, AVG(gpa)
FROM students
WHERE major = 'Computer Science'
GROUP BY major;

Mistake #4: Forgetting to Repeat Aggregate in HAVING

-- ❌ WRONG: Can't use alias in HAVING (in most databases)
SELECT major, AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
HAVING avg_gpa > 3.5;  -- Doesn't work in MySQL strict mode!

-- ✅ CORRECT: Repeat the aggregate function
SELECT major, AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5;

Mistake #5: Wrong Execution Order Assumption

-- ❌ WRONG: Thinking HAVING happens before GROUP BY
-- This doesn't make sense conceptually
SELECT major, AVG(gpa)
FROM students
HAVING AVG(gpa) > 3.5  -- Can't filter groups before they exist!
GROUP BY major;

-- ✅ CORRECT: HAVING always comes AFTER GROUP BY
SELECT major, AVG(gpa)
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5;

9. 🎯 Hands-On Practice Exercises

SELECT 
    major,
    AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.6;

SELECT 
    major,
    COUNT(*) AS student_count
FROM students
GROUP BY major
HAVING COUNT(*) >= 25;

SELECT 
    major,
    AVG(gpa) AS avg_gpa
FROM students
WHERE year >= 2023
GROUP BY major
HAVING AVG(gpa) > 3.4;

SELECT 
    major,
    AVG(gpa) AS avg_gpa,
    COUNT(*) AS student_count
FROM students
GROUP BY major
HAVING AVG(gpa) > 3.5 AND COUNT(*) > 30;

SELECT 
    major,
    MIN(gpa) AS lowest_gpa,
    AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
HAVING MIN(gpa) > 3.0;

SELECT 
    major,
    COUNT(*) AS student_count,
    AVG(gpa) AS avg_gpa
FROM students
WHERE gpa >= 3.0
GROUP BY major
HAVING COUNT(*) >= 15 AND AVG(gpa) > 3.5
ORDER BY avg_gpa DESC;

📝 Key Takeaways

• HAVING filters groups, WHERE filters rows
• Use HAVING when your condition involves aggregate functions
• HAVING comes after GROUP BY in execution order
• You can use WHERE and HAVING together (and often should!)
• WHERE runs first (more efficient), HAVING runs after grouping
• HAVING can use AND, OR for multiple conditions
• Can filter by COUNT, SUM, AVG, MIN, MAX in HAVING
• For non-aggregate conditions, use WHERE instead of HAVING for better performance
• Most databases require repeating the aggregate function in HAVING (can't use alias)
• Essential for business analytics: "Show me only high performers"