intermediateCore SQL100% Free

SQL HAVING Clause

Definition

The SQL HAVING clause filters groups produced by GROUP BY, allowing conditions on aggregate functions like COUNT, SUM, and AVG that cannot be used in WHERE.

Introduction to SQL HAVING Clause

HAVING is the WHERE clause for grouped data. Because WHERE filters individual rows before aggregation, you cannot use it to filter on aggregate results (like WHERE COUNT(*) > 5). HAVING fills this gap — it evaluates conditions after GROUP BY aggregation, filtering which groups appear in the final result.

Syntax

SQL

SELECT column, AGG_FUNC(value)
FROM table
GROUP BY column
HAVING AGG_FUNC(value) operator value;

Examples

Filter Departments with More Than 5 Employees

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY headcount DESC;

Cannot use WHERE COUNT(*) > 5 (WHERE runs before GROUP BY). HAVING filters after grouping — only returns departments with more than 5 employees.

Try in Playground

Combine WHERE and HAVING

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'Active'      -- filters rows before grouping
GROUP BY department
HAVING AVG(salary) > 75000;  -- filters groups after aggregation

WHERE and HAVING can be used together. WHERE filters active employees, GROUP BY groups them, HAVING keeps only high-earning departments.

Try in Playground

Common Mistakes

Using column aliases in HAVING (not supported in many databases — repeat the aggregate expression)

Confusing WHERE and HAVING — WHERE cannot reference aggregate functions

Forgetting that HAVING requires GROUP BY (in most databases — without GROUP BY, the entire table is one group)

Putting non-aggregate conditions in HAVING instead of WHERE (correct but inefficient)

Frequently Asked Questions

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows BEFORE GROUP BY aggregation. HAVING filters groups AFTER GROUP BY aggregation. Use WHERE for row-level conditions (WHERE salary > 50000). Use HAVING for group-level conditions on aggregate functions (HAVING COUNT(*) > 5, HAVING SUM(sales) > 100000).

Can you use HAVING without GROUP BY?

Technically yes in some databases. Without GROUP BY, the entire table is treated as a single group. HAVING COUNT(*) > 100 would return the entire result if the table has more than 100 rows. However, this is rarely useful and usually indicates a logic error.

Can HAVING reference column aliases?

Most databases do NOT allow HAVING to reference SELECT aliases. You must repeat the aggregate expression. Exception: MySQL and some others allow it. Safe universal approach: HAVING SUM(amount) > 1000 (repeat expression) rather than HAVING total_amount > 1000 (alias).

Practice This in the SQL Playground

Write real queries, see live results, and master SQL HAVING Clause hands-on. 100% free.

Open SQL Playground Browse All Lessons