Filtering Groups with HAVING

Now that you've mastered GROUP BY, it's time to control which groups make it into your results. The University schema makes this easy to visualize: maybe you want to see only the departments that teach at least four courses, or semesters where students completed more than ten classes. The HAVING clause lets you filter groups after aggregation, complementing the WHERE clause that filters individual rows before grouping.

Learning Goals

• Know when to use WHERE vs HAVING
• Filter grouped data using counts, averages, and sums
• Combine row-level and group-level filters in the same query
• Avoid common mistakes that lead to confusing results

Understanding HAVING

HAVING evaluates conditions on aggregated groups. A typical pattern looks like this:

Basic HAVING example

SELECT d.dept_name,
       AVG(c.credits) AS avg_credits
FROM course AS c
JOIN department AS d ON d.dept_id = c.dept_id
GROUP BY d.dept_name
HAVING AVG(c.credits) >= 3.5;

This query shows only departments whose courses average 3.5 credits or more.

WHERE vs HAVING

Remember the execution order: WHERE filters rows ➜ GROUP BY creates groups ➜ HAVING filters groups ➜ ORDER BY sorts the final output.

WHERE filters rows first

-- Courses worth more than 3 credits, grouped by department
SELECT d.dept_name,
       COUNT(*) AS course_count
FROM course AS c
JOIN department AS d ON d.dept_id = c.dept_id
WHERE c.credits > 3
GROUP BY d.dept_name;

HAVING filters groups after aggregation

-- Departments that teach more than 3 high-credit courses
SELECT d.dept_name,
       COUNT(*) AS course_count
FROM course AS c
JOIN department AS d ON d.dept_id = c.dept_id
WHERE c.credits > 3
GROUP BY d.dept_name
HAVING COUNT(*) > 3;

Practical HAVING Examples

Filter by number of rows in each group

Departments with at least 4 courses

SELECT d.dept_name,
       COUNT(*) AS total_courses
FROM course AS c
JOIN department AS d ON d.dept_id = c.dept_id
GROUP BY d.dept_name
HAVING COUNT(*) >= 4;

Filter by aggregate averages

Semesters with high average credit load

SELECT e.semester,
       AVG(c.credits) AS avg_credit_load
FROM enrollment AS e
JOIN course AS c ON c.course_id = e.course_id
GROUP BY e.semester
HAVING AVG(c.credits) >= 3.5;

Combine multiple aggregate conditions

Busy semesters for each department

SELECT d.dept_name,
       e.semester,
       COUNT(*) AS total_enrollments,
       SUM(c.credits) AS credit_hours_awarded
FROM enrollment AS e
JOIN course AS c ON c.course_id = e.course_id
JOIN department AS d ON d.dept_id = c.dept_id
GROUP BY d.dept_name, e.semester
HAVING COUNT(*) >= 5
   AND SUM(c.credits) >= 15;

Combining WHERE and HAVING

Strong queries filter out unnecessary rows early with WHERE, then use HAVING for final group-level checks.

Completed courses in Spring 2025 by department

SELECT d.dept_name,
       COUNT(*) AS completed_courses,
       COUNT(DISTINCT e.student_id) AS unique_students
FROM enrollment AS e
JOIN course AS c ON c.course_id = e.course_id
JOIN department AS d ON d.dept_id = c.dept_id
WHERE e.semester = 'Spring 2025'       -- Row-level filter
  AND e.grade IS NOT NULL              -- Keep only finished classes
GROUP BY d.dept_name
HAVING COUNT(*) >= 3;                  -- Group-level filter

Common Pitfalls

• Aggregates in WHERE: Conditions like WHERE COUNT(*) > 3 fail because WHERE runs before grouping.
• Missing GROUP BY: HAVING expects grouped data. Without GROUP BY, the entire dataset is treated as one group.
• Order matters: The correct order is WHERE, GROUP BY, HAVING, then ORDER BY.
• Too few row filters: Use WHERE to shrink the data before grouping; it keeps HAVING efficient.
• Alias confusion: Some engines don't let you reference SELECT aliases inside HAVING; repeat the expression if needed.

Summary

• Use WHERE for individual rows, HAVING for aggregated groups.
• Combine both clauses to answer nuanced questions about the University dataset.
• HAVING shines when you need thresholds on counts, averages, or sums after grouping.

Quiz

Show quiz

1. Which clause filters rows before grouping occurs?
   A) GROUP BY
   B) WHERE
   C) HAVING
   D) ORDER BY

2. How would you list departments that offer at least three four-credit courses?
   A) WHERE COUNT(*) >= 3
   B) HAVING COUNT(*) >= 3
   C) HAVING credits >= 4
   D) WHERE credits >= 4

3. What does HAVING AVG(c.credits) > 3.5 evaluate?
   A) Each individual course row
   B) Each group of rows created by GROUP BY
   C) Only rows with credits greater than 3.5
   D) It causes an error because AVG must be in SELECT

4. True or False: You can use both WHERE and HAVING in the same query.
   A) True
   B) False

---

Answers

1. B
2. B
3. B
4. A