Ad – 728×90
📊 Grouping & Aggregation

SQL HAVING – Filtering Groups After Aggregation

WHERE filters rows before they are grouped. HAVING filters the groups themselves after aggregation. This is a critical distinction — you cannot use aggregate functions inside a WHERE clause, which is exactly why HAVING exists.

⏱️ 15 min read 🎯 Intermediate 📅 Updated 2026

HAVING vs WHERE

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

ClauseFiltersWhen it runsCan use aggregates?
WHERE Individual rows Before GROUP BY No
HAVING Groups (after aggregation) After GROUP BY Yes
SQL
-- 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

SQL
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;
💡
HAVING can reference SELECT aliases (in some databases)

MySQL and PostgreSQL allow referencing a SELECT alias in HAVING: HAVING cnt > 5. Standard SQL requires repeating the expression: HAVING COUNT(*) > 5. For portability, use the full expression in HAVING.

Practical Examples

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

SQL
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;
▶ Result
Only departments with more than 2 active employees AND an average salary above 70,000 appear. Departments with fewer employees or lower averages are excluded by HAVING.
Ad – 336×280

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

SQL
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:

StepClauseWhat happens
1FROM / JOINBuild the full dataset from tables
2WHEREFilter individual rows
3GROUP BYCollapse rows into groups
4HAVINGFilter groups based on aggregate conditions
5SELECTChoose and compute output columns
6ORDER BYSort the result set
7LIMIT / OFFSETRestrict the number of rows returned
⚠️
Performance tip: filter early with WHERE

If you can eliminate rows before grouping, do so with WHERE. Fewer rows to group means the database engine does less work. Use HAVING only for conditions that genuinely depend on aggregate results.

Summary

📋 Summary

  • HAVING filters groups produced by GROUP BY — it runs after aggregation.
  • WHERE filters individual rows before grouping; it cannot reference aggregate functions.
  • SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
  • HAVING supports AND / OR to combine multiple group conditions.
  • Filter with WHERE whenever possible for better performance; use HAVING only for aggregate conditions.
🗄️ GROUP BY 🗄️ ROLLUP & CUBE 🗄️ WHERE Clause 🗄️ Aggregate Functions

FAQ

Can HAVING be used without GROUP BY? +

Yes, technically. Without GROUP BY the entire table is treated as a single group. HAVING COUNT(*) > 100 with no GROUP BY checks whether the table has more than 100 rows and returns either all rows or no rows. In practice this is rarely useful and is a sign of a misunderstanding — use GROUP BY for meaningful grouping.

Can I use HAVING with non-aggregate columns? +

You can, but it is redundant. HAVING department = 'Engineering' is valid SQL, but the same filter should be written as WHERE department = 'Engineering' — it runs earlier, eliminates rows before grouping, and is more efficient.

Why does SELECT alias not always work in HAVING? +

Because HAVING is evaluated at step 4 in the logical order, and SELECT aliases are assigned at step 5. Some databases (MySQL, PostgreSQL) extend standard SQL to allow aliases in HAVING, but SQL Server and Oracle require the full expression. Write HAVING COUNT(*) > 5 rather than HAVING cnt > 5 for maximum portability.

Ad – 728×90