HAVING Clause

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

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

Query: SELECT dept, COUNT(*), AVG(salary) FROM emp
       GROUP BY dept HAVING COUNT(*) >= 5 AND AVG(salary) > 60000
Execution Order:
  1. SCAN    → Read all rows from employees
  2. WHERE   → (none) — all rows pass
  3. GROUP   → Hash/Sort rows by department
  4. AGG     → Compute COUNT(*), AVG(salary) per group
  5. HAVING  → Evaluate: COUNT(*) >= 5 AND AVG(salary) > 60000
              → Groups failing this condition are DISCARDED
  6. SELECT  → Project dept, count, avg for surviving groups
  7. ORDER   → (none)
Key Insight:
  WHERE  → Evaluates BEFORE groups exist → Cannot use aggregates
  HAVING → Evaluates AFTER groups computed → Can use aggregates
Push non-aggregate filters to WHERE:
  BAD:  HAVING COUNT(*) > 5 AND every_row_has_status_active
  GOOD: WHERE status = 'active' HAVING COUNT(*) > 5
PostgreSQL allows aggregate aliases in HAVING:
  HAVING total_orders > 5  (where total_orders = COUNT(*))
  This is NOT standard SQL — may not work in other databases.

6. Visual Explanation

7. Practical Example

-- Popular products with good ratings
SELECT p.product_name, p.category,
  COUNT(r.id) AS review_count,
  ROUND(AVG(r.rating)::numeric, 1) AS avg_rating
FROM products p
JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.product_name, p.category
HAVING COUNT(r.id) >= 20 AND AVG(r.rating) >= 4.0
ORDER BY review_count DESC;
-- Duplicate detection: users with same email
SELECT LOWER(email) AS normalized_email,
  COUNT(*) AS duplicate_count,
  ARRAY_AGG(id) AS user_ids
FROM users
GROUP BY LOWER(email)
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;
-- Departments with high salary variance
SELECT department, COUNT(*) AS headcount,
  ROUND(AVG(salary)::numeric, 2) AS avg_salary,
  MAX(salary) - MIN(salary) AS salary_range
FROM employees WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) >= 5
   AND MAX(salary) - MIN(salary) > 40000
ORDER BY salary_range DESC;
-- Departments above company-wide average salary
WITH company_avg AS (
  SELECT AVG(salary) AS avg_sal FROM employees WHERE status = 'active'
)
SELECT e.department, COUNT(*) AS headcount,
  ROUND(AVG(e.salary)::numeric, 2) AS dept_avg
FROM employees e CROSS JOIN company_avg ca
WHERE e.status = 'active'
GROUP BY e.department, ca.avg_sal
HAVING AVG(e.salary) > ca.avg_sal
ORDER BY dept_avg DESC;

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.

-- BUG: Returns zero rows
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE COUNT(*) > 10
GROUP BY department;
-- ERROR: aggregate functions not allowed in WHERE
-- PostgreSQL will actually throw a syntax error here
-- FIX: Move aggregate filter to HAVING
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

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.