GROUP BY with HAVING: Filter Aggregated Results in SQL

Use GROUP BY with HAVING when you need to filter groups by an aggregate value, such as keeping only departments with more than 10 employees or emails that appear more than once. GROUP BY collapses rows into groups for COUNT, SUM, or AVG, and HAVING keeps only the groups that pass its condition. To filter individual rows before grouping, use WHERE instead.

What is the difference between WHERE and HAVING in a GROUP BY query?

WHERE filters individual rows before GROUP BY forms the groups; HAVING filters the finished groups after COUNT, SUM, or AVG have been calculated. That is why only HAVING can test an aggregate such as COUNT(*) > 10.

WHERE filters rows before grouping, so this counts only the employees earning more than 50,000 in each department:

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

HAVING filters groups after aggregation, so this keeps only departments with more than 10 employees:

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

Should a condition go in WHERE or HAVING?

Put a condition in WHERE if it tests a column value on each row, such as salary > 50000, and in HAVING if it tests an aggregate, such as AVG(salary) > 75000. Keeping row conditions in WHERE also makes the query cheaper, because fewer rows reach GROUP BY.

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

Can you use WHERE and HAVING in the same query?

Yes. 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

What does a GROUP BY HAVING query return?

It returns one row per group that passes the HAVING condition; groups that fail it are dropped from the result entirely. In this six-row table, grouping by department produces three groups, and HAVING COUNT(*) >= 2 removes Support because it has only one employee.

-- employees
-- name   department    salary
-- Ana    Sales          50000
-- Ben    Sales          60000
-- Cara   Sales          55000
-- Dev    Engineering    95000
-- Eli    Engineering    85000
-- Fay    Support        45000

SELECT department, COUNT(*) AS emp_count, SUM(salary) AS payroll
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY department;

-- department    emp_count   payroll
-- Engineering   2           180000
-- Sales         3           165000

Now add WHERE salary > 50000 to the same query. Ana's row is removed before grouping, so Sales is counted as 2 employees instead of 3, and Fay's row is removed too, so Support never forms a group at all. Engineering and Sales still pass HAVING COUNT(*) >= 2, but with HAVING COUNT(*) >= 3 the query would now return no rows.

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;

Can you use a column alias in HAVING?

Not portably. PostgreSQL and SQL Server reject HAVING emp_count > 10 because HAVING is logically evaluated before the SELECT list assigns aliases, while MySQL and SQLite accept it as an extension. Repeat the aggregate expression instead and the query runs everywhere.

-- Fails in PostgreSQL and SQL Server
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING emp_count > 10;

-- Portable
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

Why can't you use COUNT() in a WHERE clause?

Because WHERE runs on individual rows before any groups exist, so there is no count to compare yet. The database rejects the query (SQLite, for example, reports misuse of aggregate: COUNT()); move the condition into HAVING after GROUP BY.

-- Error: aggregate in WHERE
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE COUNT(*) > 1
GROUP BY department;

-- Fix: filter the groups with HAVING
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;