HAVING: Filtering Group Aggregates

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           -- One HAVING clause, two conditions joined by AND
   AND AVG(rating) > 4.0       -- Both must be true for group to survive
ORDER BY user_id

Key Points:

• 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 rating > 3.5 · 9 rows → 7 rows (crossed-out rows fail the test; NULL is UNKNOWN, also removed)

Step 2: GROUP BY on Filtered Data

Step 3: HAVING COUNT(*) >= 2 · 3 groups → 2 rows (HAVING crosses out groups that are too small)

Output

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

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

• 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