HAVING
Filters grouped results after GROUP BY using aggregate conditions.
SELECT group_col, COUNT(*) FROM table_name GROUP BY group_col HAVING COUNT(*) >= 10;This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.
What it does
Overview
Filters grouped results after GROUP BY using aggregate conditions.
The HAVING clause serves as a post-aggregation filter, allowing developers to restrict rows returned by a GROUP BY clause based on specified conditions involving aggregate functions such as SUM(), AVG(), or COUNT(). Unlike the WHERE clause, which filters individual records before the grouping process occurs, HAVING is evaluated after the data has been summarized into groups. This distinction is critical for performance; applying filters in WHERE is generally more efficient as it reduces the input size for the GROUP BY operation. In standard SQL, every column referenced in the HAVING clause must either be an aggregate function or appear in the GROUP BY clause. Note that for date-based comparisons within aggregate filters, using standard ISO 8601 strings (YYYY-MM-DD) is recommended for reliable ordering across different database engines. Edge cases include using HAVING without a GROUP BY, in which case the entire result set is treated as a single group, which is often used to perform global validations on a table's aggregate state.
Quick reference
Syntax
SELECT group_col, COUNT(*) FROM table_name GROUP BY group_col HAVING COUNT(*) >= 10;Inputs
Parameters
See it in practice
Examples
Filtering Group Totals
SELECT department_id, SUM(salary) AS total_payroll
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 100000;department_id | total_payroll --------------|-------------- 101 | 155000 104 | 210000
Returns departments where the total salary expenditure is greater than 100,000. This requires HAVING because SUM() is an aggregate function that cannot be used in WHERE.
Identifying Duplicate Records
SELECT username, COUNT(*)
FROM accounts
GROUP BY username
HAVING COUNT(*) > 1;username | count ---------|------ jdoe | 2 admin | 3
A common pattern for data cleaning; this identifies which usernames appear multiple times in the accounts table by filtering groups with a count greater than one.
Complex Multi-Aggregate Filtering
SELECT product_type, AVG(rating), COUNT(review_id)
FROM reviews
GROUP BY product_type
HAVING COUNT(review_id) >= 50 AND AVG(rating) >= 4.5;product_type | avg | count -------------|-----|------ Electronics | 4.7 | 120 Books | 4.8 | 85
Filters for categories that are both popular (at least 50 reviews) and highly rated (4.5 average or higher).
Debug faster
Common Errors
LogicError
Cause: Attempting to use an aggregate function like COUNT() or MAX() inside the WHERE clause.
Fix: Aggregate calculations must be placed in the HAVING clause because WHERE is processed before groups are formed by the database engine.
SELECT id FROM items WHERE COUNT(id) > 5;GroupingError
Cause: Filtering by a non-aggregated column in the HAVING clause that is not included in the GROUP BY clause.
Fix: Move non-aggregated filters to the WHERE clause to improve performance and satisfy SQL standards, or add the missing column to the GROUP BY list.
SELECT category, SUM(price) FROM products GROUP BY category HAVING status = 'active';Runtime support
Compatibility
Source: MDN Web Docs
Common questions
Frequently Asked Questions
Filters grouped results after GROUP BY using aggregate conditions.
ondition: Condition using aggregates (e.g., COUNT(*) > 1).
LogicError: Aggregate calculations must be placed in the HAVING clause because WHERE is processed before groups are formed by the database engine. GroupingError: Move non-aggregated filters to the WHERE clause to improve performance and satisfy SQL standards, or add the missing column to the GROUP BY list.