SQL HAVING – Filtering Groups After Aggregation

HAVING vs WHERE

The key distinction: WHERE filters rows, HAVING filters groups.

-- WRONG: cannot use aggregate in WHERE
SELECT department, COUNT(*) AS cnt
FROM employees
WHERE COUNT(*) > 5          -- ERROR: aggregate functions not allowed in WHERE
GROUP BY department;

-- CORRECT: use HAVING for post-aggregation filtering
SELECT department, COUNT(*) AS cnt
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;        -- filters groups, not rows

HAVING Syntax

SELECT   column1, aggregate_function(column2) AS alias
FROM     table_name
WHERE    row_condition          -- optional: filter rows first
GROUP BY column1
HAVING   group_condition        -- filter groups after aggregation
ORDER BY column1;

Practical Examples

Find departments with more than 2 employees and an average salary above 70,000:

SELECT
    department,
    COUNT(*)        AS headcount,
    AVG(salary)     AS avg_salary
FROM employees
WHERE status = 'active'          -- filter to active employees first
GROUP BY department
HAVING COUNT(*) > 2
   AND AVG(salary) > 70000
ORDER BY avg_salary DESC;

Find product categories where total revenue exceeded 100,000 last year:

SELECT
    category,
    SUM(price * quantity) AS total_revenue
FROM order_items
JOIN products USING (product_id)
WHERE YEAR(order_date) = 2025
GROUP BY category
HAVING SUM(price * quantity) > 100000
ORDER BY total_revenue DESC;

SQL Execution Order

Understanding the logical order SQL processes a query is essential to writing correct HAVING clauses:

Summary

HAVING vs WHERE: Filter Groups, Not Rows

They look similar but run at different stages. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. That's why aggregate functions like COUNT() work in HAVING but not WHERE.

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE status = 'paid'          -- 1. drop unpaid rows FIRST
GROUP BY customer_id           -- 2. group what remains
HAVING COUNT(*) >= 3           -- 3. keep only groups with 3+ orders
ORDER BY total DESC;

Performance tip: filter as much as possible in WHERE first — it shrinks the data before the expensive grouping step. Only push a condition to HAVING when it genuinely depends on an aggregate (a per-group count/sum). Putting a plain row condition in HAVING still works but makes the database group rows it could have discarded earlier.

Interview Questions

• What does the HAVING clause do?
• What is the difference between WHERE and HAVING?
• Can you use aggregates in a WHERE clause?
• Can HAVING be used without GROUP BY?
• What is the execution order of WHERE versus HAVING?

Related Topics