GROUP BY with HAVING: Filter Aggregated Results in SQL

Use GROUP BY to collapse rows into groups for COUNT, SUM, or AVG. Add HAVING only when you need to filter those groups by an aggregate value. WHERE is different: it filters individual rows before any grouping happens.

The Key Difference

WHERE filters rows before grouping:

SELECT department, COUNT(*) as emp_count
FROM employees
WHERE salary > 50000  -- Filters before grouping
GROUP BY department;

HAVING filters groups after aggregation:

SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;  -- Filters after grouping

When to Use Each

Use WHERE for conditions on individual row values and HAVING for conditions on aggregate values.

• WHERE: Filter individual rows (use column names directly)
• HAVING: Filter aggregated results (use aggregate functions)

Combining Both

A query can use both. Clause order is fixed: WHERE comes before GROUP BY, and HAVING comes after it. WHERE filters rows, GROUP BY groups what is left, and HAVING filters the groups.

-- Find departments with avg salary > 75k among employees making > 40k
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE salary > 40000        -- Filter rows first
GROUP BY department
HAVING AVG(salary) > 75000; -- Filter groups second

Common Use Cases

A typical HAVING filter compares an aggregate to a threshold, such as a minimum order count, or a count greater than 1 to find duplicates.

Find High-Volume Customers

SELECT customer_id, COUNT(*) as order_count, SUM(total) as revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) >= 5 OR SUM(total) >= 10000;

Identify Duplicate Records

SELECT email, COUNT(*) as duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;