Lesson 17 — Filtering Groups

HAVING

HAVING filters the result of GROUP BY. It is WHERE for groups. Without it you cannot filter on aggregated values like 'courses with average mark above 70'.

SQL — Basic HAVING 
-- HAVING filters groups (after GROUP BY):
SELECT
    course,
    COUNT(*) AS learner_count,
    AVG(mark) AS avg_mark
FROM   learners
GROUP  BY course
HAVING AVG(mark) > 70;   -- only courses averaging above 70%

-- HAVING COUNT — groups with more than 5 learners:
SELECT
    city,
    COUNT(*) AS learner_count
FROM   learners
GROUP  BY city
HAVING COUNT(*) > 5
ORDER  BY learner_count DESC;
SQL — WHERE + GROUP BY + HAVING 
-- WHERE + GROUP BY + HAVING (all three together):
SELECT
    course,
    COUNT(*) AS active_learner_count,
    ROUND(AVG(mark), 1) AS avg_mark
FROM   learners
WHERE  is_active = TRUE          -- 1: filter rows (active only)
GROUP  BY course                  -- 2: group the active rows
HAVING COUNT(*) >= 3              -- 3: only courses with 3+ active learners
   AND AVG(mark) >= 50            -- 4: AND average pass rate
ORDER  BY avg_mark DESC;          -- 5: sort the groups
SQL — Complex HAVING 
-- HAVING with CASE — complex group filter:
SELECT
    course,
    COUNT(*) AS total,
    COUNT(CASE WHEN mark >= 50 THEN 1 END) AS passed,
    ROUND(
        COUNT(CASE WHEN mark >= 50 THEN 1 END) * 100.0 / COUNT(*)
    , 1) AS pass_rate
FROM   learners
WHERE  mark IS NOT NULL
GROUP  BY course
HAVING COUNT(*) >= 5               -- only courses with at least 5 graded learners
   AND COUNT(CASE WHEN mark >= 50 THEN 1 END) * 1.0 / COUNT(*) < 0.6
   -- pass rate below 60% — courses that may need facilitator attention
ORDER  BY pass_rate ASC;

Practice Task

Your Turn

Write queries: (1) cities with more than 3 learners; (2) courses where at least one learner scored above 90; (3) courses with average mark between 60 and 75 and at least 4 learners; (4) show only courses where the number of failing learners (mark < 50) exceeds the number of passing learners — these courses need intervention.

Common Mistakes

  • Using WHERE instead of HAVING for aggregate conditions — WHERE AVG(mark) > 70 is a syntax error; aggregates go in HAVING.
  • Using HAVING without GROUP BY — technically allowed in some databases but usually wrong.
  • HAVING can reference aliases in GROUP BY in some engines (MySQL) but not in standard SQL — use the expression.
  • HAVING condition on a column not in GROUP BY — may work but produces confusing semantics.

Professional Tip

The order of clause execution is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Knowing this helps you understand which values are available at each step.

Mini Quiz

Why can't you use WHERE AVG(mark) > 70?