Chapter 39: PostgreSQL HAVING Clause
The HAVING clause.
You already know:
- WHERE filters individual rows before grouping
- GROUP BY creates groups and lets you calculate aggregates per group (COUNT, AVG, SUM…)
- But what if you want to filter the groups themselves — keep only those groups that satisfy a condition?
That’s exactly what HAVING is for.
1. What does HAVING actually do? (Teacher’s plain & honest explanation)
HAVING filters groups (after grouping and aggregation has happened).
Think of the query execution order in PostgreSQL:
- FROM + JOINs → get all candidate rows
- WHERE → filter individual rows
- GROUP BY → split remaining rows into groups
- Aggregates (COUNT, AVG…) → calculated for each group
- HAVING → throw away entire groups that don’t satisfy the condition
- SELECT → choose which columns/expressions to show
- ORDER BY / LIMIT → final sorting & limiting
HAVING can only use:
- Columns that appear in the GROUP BY clause
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX…)
- Expressions based on the above
HAVING cannot see individual row columns that are not grouped (this is the #1 confusion point).
2. Basic syntax skeleton (memorize this flow)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT grouping_column, aggregate_function(...), ... FROM table [WHERE row_filter] GROUP BY grouping_column(s) HAVING group_condition_using_aggregates_or_grouped_columns [ORDER BY …] [LIMIT …]; |
3. Real, detailed examples (using our students table)
Current data (with some added rows for better examples):
| id | first_name | city | enrollment_year | gpa | fees_paid | courses_enrolled |
|---|---|---|---|---|---|---|
| 1 | Aarav | Hyderabad | 2025 | 8.95 | TRUE | 2 |
| 2 | Priya | Secunderabad | 2024 | 9.60 | FALSE | 1 |
| 3 | Rahul | Hyderabad | 2025 | 7.85 | FALSE | 0 |
| 4 | Sneha | Hyderabad | 2025 | 9.20 | TRUE | 1 |
| 5 | Vikram | Hyderabad | 2025 | 8.40 | TRUE | 1 |
| 6 | Meera | Secunderabad | 2025 | 10.00 | TRUE | 3 |
| 7 | Kiran | Hyderabad | 2024 | 8.10 | TRUE | 2 |
Example 1: HAVING vs WHERE – classic confusion demo
SQL
|
0 1 2 3 4 5 6 7 8 9 10 |
-- Wrong: trying to filter on aggregate in WHERE SELECT city, AVG(gpa) AS avg_gpa FROM students WHERE AVG(gpa) >= 9.0 -- ERROR! GROUP BY city; |
Correct → move the aggregate condition to HAVING:
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT city, ROUND(AVG(gpa), 2) AS avg_gpa, COUNT(*) AS student_count FROM students GROUP BY city HAVING AVG(gpa) >= 9.0; |
Result:
text
|
0 1 2 3 4 5 6 7 8 |
city | avg_gpa | student_count -------------|---------|--------------- Secunderabad | 9.80 | 2 |
→ Only cities where average GPA is ≥ 9.0 are kept
Example 2: HAVING with COUNT (most common real-world use)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT enrollment_year, COUNT(*) AS student_count, ROUND(AVG(gpa), 2) AS avg_gpa FROM students GROUP BY enrollment_year HAVING COUNT(*) >= 3 -- only years with 3+ students AND AVG(gpa) >= 8.5 -- and decent average ORDER BY enrollment_year DESC; |
Result (example):
text
|
0 1 2 3 4 5 6 7 8 |
enrollment_year | student_count | avg_gpa ────────────────┼───────────────┼──────── 2025 │ 5 │ 8.88 |
Example 3: HAVING + multiple aggregates + multiple grouping columns
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT city, fees_paid, COUNT(*) AS student_count, ROUND(AVG(gpa), 2) AS avg_gpa, SUM(CASE WHEN courses_enrolled > 0 THEN 1 ELSE 0 END) AS students_with_courses FROM students GROUP BY city, fees_paid HAVING COUNT(*) >= 2 AND AVG(gpa) >= 8.5 AND SUM(CASE WHEN courses_enrolled > 0 THEN 1 ELSE 0 END) >= 1 ORDER BY city, fees_paid; |
Result (example):
text
|
0 1 2 3 4 5 6 7 8 |
city | fees_paid | student_count | avg_gpa | students_with_courses -------------┼───────────┼───────────────┼─────────┼────────────────────── Hyderabad | TRUE | 3 | 8.85 | 3 |
→ Only groups (city + fees_paid combinations) that meet all three conditions
Example 4: HAVING with JOIN (very real reporting pattern)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT c.name AS course_name, COUNT(e.student_id) AS enrollment_count, ROUND(AVG(s.gpa), 2) AS avg_gpa_of_enrolled FROM courses c LEFT JOIN enrollments e ON c.id = e.course_id LEFT JOIN students s ON s.id = e.student_id GROUP BY c.id, c.name HAVING COUNT(e.student_id) > 0 -- only courses that have at least 1 enrollment AND AVG(s.gpa) >= 8.5 -- and enrolled students have good GPA ORDER BY enrollment_count DESC; |
→ Very typical “course performance report”
4. HAVING vs WHERE – side-by-side comparison (very important!)
| Clause | Filters… | Can use aggregates? | Runs… | Typical use case example |
|---|---|---|---|---|
| WHERE | Individual rows | No | Before GROUP BY | WHERE enrollment_year = 2025 |
| HAVING | Groups (after aggregation) | Yes | After GROUP BY | HAVING COUNT(*) >= 5 or HAVING AVG(gpa) > 9.0 |
Very common mistake:
SQL
|
0 1 2 3 4 5 6 7 8 9 |
SELECT city, COUNT(*) FROM students WHERE COUNT(*) > 2 -- ERROR! GROUP BY city; |
→ Fix: move to HAVING
5. Quick cheat-sheet (keep in your notes)
| Goal | Typical HAVING condition | Equivalent WHERE (if possible) |
|---|---|---|
| Groups with enough rows | HAVING COUNT(*) >= 10 | — (cannot) |
| Groups with good average | HAVING AVG(salary) > 50000 | — (cannot) |
| Groups with at least one special member | HAVING SUM(CASE WHEN is_active THEN 1 ELSE 0 END) >= 1 | — (cannot directly) |
| Exclude small/empty groups | HAVING COUNT(*) > 0 | — (but LEFT JOIN + WHERE IS NULL can simulate) |
Your mini homework right now
- Group students by city — show count + avg GPA
- Add HAVING to keep only cities with average GPA ≥ 9.0
- Group by enrollment_year and fees_paid — keep only combinations with at least 2 students
- Try writing a query with HAVING on a non-grouped column → see the error
Next class?
Tell me:
- Want HAVING vs WHERE more tricky examples?
- GROUP BY + JOIN + HAVING real reporting query?
- Advanced grouping: GROUPING SETS, ROLLUP, CUBE?
- Aggregates without GROUP BY vs with GROUP BY?
Your teacher is ready — what’s the next topic? 🚀
