HAVING: Filtering Group Aggregates

Core Concept

• Example: Identify users with an average rating above 4.0. This requires a GROUP BY operation first, followed by a HAVING clause to filter the groups.

HAVING is your tool for evaluating group aggregates. It's the big picture filter, focusing on counts, averages, and sums, unlike WHERE, which zeroes in on individual rows.

Simple HAVING

-- Find users whose average rating exceeds 4.0
SELECT                         -- Last: Select columns to output
    user_id,
    COUNT(*) AS listen_count,
    AVG(rating) AS avg_rating
FROM Listens                   -- First: Get rows
GROUP BY user_id               -- Next: Create groups
HAVING AVG(rating) > 4.0       -- Then: Filter groups by aggregate condition
ORDER BY user_id               -- Last: Sort surviving groups

• GROUP BY first, HAVING second

• FALSE = entire group gone

• No partial elimination

HAVING with COUNT

-- Find songs played at least twice
SELECT 
    song_id,
    COUNT(*) AS play_count,
    AVG(rating) AS avg_rating
FROM Listens
GROUP BY song_id               -- Group by each song
HAVING COUNT(*) >= 2           -- Keep only groups with 2+ rows
ORDER BY song_id               -- Sort output

Key Points:

• Unpopular songs eliminated

• Threshold: 2+ plays

• NULLs counted in COUNT(*)

HAVING with Multiple Conditions

-- Find active users (3+ listens) with high average ratings (>4.0)
SELECT 
    user_id,
    COUNT(*) AS listen_count,
    AVG(rating) AS avg_rating,
    MIN(rating) AS min_rating
FROM Listens
GROUP BY user_id
HAVING COUNT(*) >= 3           -- Multiple HAVING conditions with AND
   AND AVG(rating) > 4.0       -- Both must be true for group to survive
ORDER BY user_id

Step 1: Calculate Group Aggregates

Step 2: Evaluate HAVING Conditions

Output

Key Points:

• AND: both must pass

• Failing one = eliminated

• Mix any aggregates

WHERE and HAVING

-- Find users with 2+ ratings above 3.5

SELECT 
    user_id,
    COUNT(*) AS high_rating_count,
    AVG(rating) AS avg_high_rating
FROM Listens
WHERE rating > 3.5             -- 1st: WHERE filters individual rows
GROUP BY user_id               -- 2nd: GROUP BY forms groups from survivors
HAVING COUNT(*) >= 2           -- 3rd: HAVING filters entire groups
ORDER BY user_id               -- 4th: ORDER BY sorts final results

Step 1: WHERE Filters Rows

Step 2: GROUP BY on Filtered Data

Step 3: HAVING Filters Groups

Output

Key Points:

• WHERE filters rows (pre-GROUP)

• HAVING filters groups (post-GROUP)

Key Rules

1. Execution Order: HAVING runs after GROUP BY.

2. Aggregate Conditions: Can use aggregate functions like AVG(), COUNT()

3. Group-Level Filter: Operates on entire groups, not individual rows

4. Requires GROUP BY: HAVING only makes sense with grouped data

5. Different from WHERE: WHERE filters rows, HAVING filters groups

SQL Execution Pipeline

Input:  FROM
Logic:  WHERE → GROUP BY → HAVING
        (no aggregates) → (form groups) → (aggregates exist!)
Output: SELECT/ORDER BY

Common Patterns

• Aggregate Filters: HAVING COUNT(*) > 2 keeps groups with 3+ rows

• Average Checks: HAVING AVG(rating) > 4.0 filters by group average

• Multiple Conditions: HAVING COUNT(*) > 1 AND AVG(rating) > 4.0

• With WHERE: WHERE filters rows first, then HAVING filters groups

• Column References: Can use GROUP BY columns or aggregates

Common Mistakes

HAVING on rows

-- WRONG: HAVING does not work on individual rows
SELECT * FROM Listens
HAVING rating > 4.0

-- CORRECT: Use WHERE to filter individual rows
SELECT * FROM Listens
WHERE rating > 4.0

Problem: HAVING only works with grouped data

The HAVING Alias Problem

-- WRONG: Alias not yet created when HAVING runs
SELECT user_id, AVG(rating) AS avg_r
FROM Listens
GROUP BY user_id
HAVING avg_r > 4.0

-- CORRECT: Repeat the aggregate expression in HAVING
SELECT user_id, AVG(rating) AS avg_r
FROM Listens
GROUP BY user_id
HAVING AVG(rating) > 4.0

Problem: HAVING executes before SELECT creates aliases

Wrong Filter, Wrong Place

-- WRONG: HAVING can't access individual row values
SELECT user_id, COUNT(*)
FROM Listens
GROUP BY user_id
HAVING rating > 4.0            -- Error: Which rating? Group has many!

-- CORRECT: WHERE for row conditions, HAVING for group conditions
SELECT user_id, COUNT(*)
FROM Listens
WHERE rating > 4.0             -- Filter rows BEFORE grouping
GROUP BY user_id
HAVING COUNT(*) > 2            -- Then filter groups by aggregate

Problem: HAVING should use aggregate functions or GROUP BY columns

NULL Aggregate Confusion

-- Surprising: AVG ignores NULL ratings
SELECT user_id, AVG(rating)
FROM Listens
GROUP BY user_id
HAVING AVG(rating) > 4.0       -- AVG skips NULLs (may mislead!)

-- Explicit: Show total vs rated counts for clarity
SELECT user_id, 
       COUNT(*) AS total,       -- Includes NULL rows
       COUNT(rating) AS rated,  -- Excludes NULL ratings
       AVG(rating) AS avg_rating
FROM Listens
GROUP BY user_id
HAVING COUNT(rating) > 0       -- Ensure at least 1 non-NULL rating
   AND AVG(rating) > 4.0       -- Then check average

Problem: AVG ignores NULL, which affects HAVING conditions