HAVING Clause

You now know how to group rows with GROUP BY and summarize them with aggregate functions. But what if you want to keep only certain groups? For example, "Show me only departments with more than 5 employees" or "Show me customers who have spent over $1,000." That is what HAVING is for.

The Problem: Filtering Groups

Suppose you want departments with more than 3 employees. You might try this:

-- This will NOT work!
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE COUNT(*) > 3
GROUP BY department;

This fails with an error: aggregate functions are not allowed in WHERE. The reason is that WHERE filters individual rows before grouping happens, so COUNT(*) has not been calculated yet.

The Solution: HAVING

HAVING filters groups after GROUP BY has done its work:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

This works because:

1. GROUP BY department creates the groups
2. COUNT(*) is calculated for each group
3. HAVING COUNT(*) > 3 removes groups that do not meet the condition
4. Only the surviving groups appear in the result

WHERE vs. HAVING

This distinction is critical and comes up frequently:

Think of it this way:

• WHERE decides which rows go into the groups
• HAVING decides which groups appear in the result

Using WHERE and HAVING Together

You can (and often should) use both clauses in the same query. They work at different stages:

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

Here is the execution order:

1. FROM employees -- start with all employees
2. WHERE is_active = true -- keep only active employees
3. GROUP BY department -- group the active employees by department
4. HAVING AVG(salary) > 60000 -- keep only groups where the average salary exceeds 60,000
5. SELECT -- return the department and average salary

Common HAVING Patterns

Groups with a Minimum Count

Find categories that have at least 3 products:

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) >= 3;

Groups Exceeding a Threshold

Find customers whose total spending exceeds a threshold:

SELECT customer_name, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_name
HAVING SUM(total_price) > 500;

Groups Within a Range

Find departments where the average salary falls within a specific range:

SELECT department, ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) BETWEEN 50000 AND 80000;

Multiple HAVING Conditions

You can combine conditions with AND and OR:

SELECT department,
       COUNT(*) AS num_employees,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2
   AND AVG(salary) > 50000;

This keeps only departments that have at least 2 employees and an average salary above 50,000.

HAVING with ORDER BY

You can sort the filtered groups with ORDER BY:

SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000
ORDER BY total_payroll DESC;

This shows departments with over 100,000 in total payroll, sorted from highest to lowest.

Full Query Execution Order

Here is the complete execution order with all clauses:

1. FROM        -- Pick the table(s)
2. WHERE       -- Filter individual rows
3. GROUP BY    -- Create groups from remaining rows
4. HAVING      -- Filter groups
5. SELECT      -- Choose columns and compute expressions
6. ORDER BY    -- Sort the final output
7. LIMIT       -- Restrict how many rows to return

Memorizing this order helps you understand why certain things work and others do not. For example, you cannot use a column alias (defined in SELECT) inside HAVING because HAVING runs before SELECT.

-- This may not work in all databases (PostgreSQL allows it as an extension):
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING emp_count > 3;

-- The standard and always-safe way:
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

HAVING Without GROUP BY

A less common but valid usage is HAVING without GROUP BY. In this case, the entire table is treated as one group:

SELECT COUNT(*) AS total_employees
FROM employees
HAVING COUNT(*) > 5;

This returns the total count only if there are more than 5 employees. If there are 5 or fewer, the query returns no rows.

Try It Yourself

1. Find product categories where the most expensive product costs more than 50. Show the category and the max price.

SELECT category, MAX(price) AS max_price
FROM products
GROUP BY category
HAVING MAX(price) > 50;

2. Find order statuses that have more than 2 orders. Show the status and order count, sorted by count descending.

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
HAVING COUNT(*) > 2
ORDER BY order_count DESC;

3. Find departments where the total payroll (sum of salaries) is above 100,000 and there are at least 2 employees. Show department, total payroll, and employee count.

SELECT department,
       SUM(salary) AS total_payroll,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000
   AND COUNT(*) >= 2
ORDER BY total_payroll DESC;

4. Find customers who have placed more than 1 order with an average order value above 50.

SELECT customer_name,
       COUNT(*) AS order_count,
       ROUND(AVG(total_price), 2) AS avg_order_value
FROM orders
GROUP BY customer_name
HAVING COUNT(*) > 1
   AND AVG(total_price) > 50;