HAVING Clause

Last Updated: June 12, 2026 • 12 min read

The HAVING clause filters groups produced by GROUP BY based on conditions that involve aggregate values. While WHERE filters individual rows before grouping, HAVING filters the groups themselves after aggregation has been computed. Think of it as a "WHERE for groups" — it lets you answer questions like "which departments have more than 10 employees?" or "which products have an average rating above 4.0?".

1. Introduction

HAVING is the aggregate-aware filter for grouped results. It runs after GROUP BY computes aggregates, so it can reference COUNT, SUM, AVG, MIN, MAX in its conditions. WHERE cannot do this because it runs before grouping. The mnemonic: "WHERE filters rows, HAVING filters groups."

2. Why It Matters

Quality filtering: Show only products with 20+ reviews AND 4+ star average — impossible without HAVING.
Duplicate detection: HAVING COUNT(*) > 1 finds duplicate values grouped by key columns.
Threshold reporting: Departments above a salary benchmark, customers with lifetime value over $10K.
Performance: Put non-aggregate conditions in WHERE (filters early) and aggregate conditions in HAVING (filters after grouping).

3. Real-World Analogy

HAVING is like a quality inspector who checks batch summaries. The factory first sorts products into batches by type (GROUP BY). Then the inspector checks each batch: "Does this batch have at least 10 items? (HAVING COUNT(*) >= 10)" and "Is the average weight within spec? (HAVING AVG(weight) BETWEEN 95 AND 105)". Batches that fail either check are rejected. The inspector can't examine individual items — they only see the batch summary numbers.

4. How It Works

Aspect	WHERE	HAVING
When	Before GROUP BY	After GROUP BY
Filters	Individual rows	Groups of rows
Aggregates	Not allowed	Allowed
Performance	Better (reduces early)	After all grouping done

Common HAVING patterns:

Minimum group size: HAVING COUNT(*) >= N
Aggregate thresholds: HAVING AVG(salary) > 80000
Range checks: HAVING MAX(salary) - MIN(salary) > 50000
Duplicate detection: HAVING COUNT(*) > 1
Subquery comparison: HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)

5. Internal Architecture

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Using WHERE for aggregate conditions: WHERE COUNT(*) > 5 is invalid. Move to HAVING.
Non-aggregate filters in HAVING: HAVING status = 'active' works but is slower — push to WHERE for early filtering.
Relying on aliases in HAVING: PostgreSQL allows HAVING total_orders > 5 (alias for COUNT(*)), but other databases may not. Use the full expression for portable SQL.

Interview Insight

"Find customers with 5+ orders AND $1000+ total spend." Answer: HAVING COUNT(*) > 5 AND SUM(total) > 1000. Interviewers watch for whether you put SUM(total) > 1000 in WHERE (wrong) or HAVING (correct).

Common Pitfall

Column aliases in HAVING: PostgreSQL allows it, but MySQL/SQL Server may not. For portability, repeat the aggregate expression: HAVING COUNT(*) > 5 instead of HAVING order_count > 5.

9. Quick Quiz

Q1: Which clause should contain the condition status = 'active'?

A) WHERE B) HAVING C) Either works, WHERE is better

Answer: C — Either works but WHERE is better because it filters before grouping (less work).

Q2: Can HAVING be used without GROUP BY?

A) No, syntax error B) Yes, it filters the single-row aggregate result C) Only in PostgreSQL

Answer: B — Without GROUP BY, the entire table is one group. HAVING filters that single result.

10. Scenario-Based Challenge

Find Anomalous Departments

Write a query that finds departments where: (1) at least 5 employees, (2) average salary is more than 20% above the company average, (3) the salary range (MAX - MIN) exceeds $50K, AND (4) at least one employee was hired in the last 6 months. Combine WHERE (recent hire filter) with HAVING (aggregate conditions) and a CTE for the company-wide average.

11. Debugging Exercise

This query returns zero rows, but there are definitely departments with more than 10 employees. Why?

12. Interview Questions

Q: When should you use HAVING instead of WHERE?

A: Use HAVING for conditions involving aggregate functions (COUNT, SUM, AVG, etc.). Use WHERE for conditions on individual row values. If the condition doesn't involve an aggregate, put it in WHERE for better performance.

Q: How do you detect duplicate records using GROUP BY and HAVING?

A: Group by the columns that should be unique, then filter with HAVING COUNT(*) > 1: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. Add ARRAY_AGG(id) to see which IDs are duplicated.

Q: Can HAVING reference a subquery?

A: Yes. Example: HAVING AVG(salary) > (SELECT AVG(salary) FROM employees) finds departments above the company average. You can also use CTEs with CROSS JOIN for the same pattern.

13. Production Considerations

Push filters early: Non-aggregate conditions belong in WHERE, not HAVING. This reduces the rows that need to be grouped, improving performance.
Combine with CTEs: For complex multi-level filtering (e.g., groups above a computed benchmark), use a CTE for the benchmark and CROSS JOIN it with the main query.
Monitor HAVING performance: HAVING runs after all groups are computed. If you have millions of groups but only need a few, consider whether a WHERE clause can reduce the input first.
Portability: Don't rely on alias references in HAVING. Use the full aggregate expression for cross-database compatibility.