SQL HAVING Clause: Filter Groups After GROUP BY

GROUP BY turns rows into groups. But what if you only want some groups: departments with more than one person, customers who ordered more than once, categories worth over 50,000? WHERE cannot help, because it runs before the groups exist. That is the job of HAVING.

The example table#

Syntax#

SELECT column, AGGREGATE(column)
FROM table_name
WHERE row_condition
GROUP BY column
HAVING group_condition
ORDER BY ...;

HAVING comes right after GROUP BY, and its condition usually contains an aggregate function.

Filter on a count#

Departments with at least two employees:

SELECT department, COUNT(*) AS employees
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2;

department  | employees
------------+----------
Engineering | 2
Sales       | 2

Marketing was grouped like the others, then removed by HAVING because its count is 1.

Filter on an average#

Departments whose average salary is above 60,000:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

department  | avg_salary
------------+-----------
Engineering | 79500

WHERE and HAVING together#

They filter at different moments, and you can use both:

SELECT department,
       COUNT(*)    AS people,
       SUM(salary) AS payroll
FROM employees
WHERE city <> 'Delhi'          -- 1. remove rows
GROUP BY department            -- 2. form groups
HAVING SUM(salary) > 50000     -- 3. remove groups
ORDER BY payroll DESC;

department  | people | payroll
------------+--------+--------
Engineering | 2      | 159000
Sales       | 1      | 52000

Step by step:

1. WHERE removes Priya (Delhi) before grouping, so Sales contains only Asha.
2. Groups are formed: Engineering 159,000, Sales 52,000, Marketing 46,000.
3. HAVING removes Marketing, because 46,000 is not above 50,000.

Several conditions in HAVING#

Combine conditions with AND and OR, just like in WHERE:

SELECT city, COUNT(*) AS people, AVG(salary) AS avg_salary
FROM employees
GROUP BY city
HAVING COUNT(*) = 1 AND AVG(salary) > 60000
ORDER BY city;

city      | people | avg_salary
----------+--------+-----------
Bengaluru | 1      | 78000
Delhi     | 1      | 61000
Pune      | 1      | 81000

A real-world example: repeat customers#

Customers who placed more than one order, with their total spend:

SELECT customer_id,
       COUNT(*)    AS orders,
       SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

customer_id | orders | total
------------+--------+------
1           | 2      | 370

Using aliases in HAVING#

Standard SQL evaluates HAVING before SELECT, so an alias like employees is not available yet. PostgreSQL and SQL Server require the full expression (HAVING COUNT(*) >= 2). MySQL and SQLite accept the alias (HAVING employees >= 2). Repeating the expression works everywhere.

Common mistakes#

Practice#

SELECT category,
       COUNT(*)   AS products,
       MAX(price) AS top_price
FROM products
GROUP BY category
HAVING MAX(price) > 5000
ORDER BY top_price DESC;

category    | products | top_price
------------+----------+----------
Electronics | 3        | 55000
Furniture   | 2        | 7000

Key takeaway#