SQL HAVING

What does the SQL HAVING clause do?

HAVING filters grouped rows after aggregation, returning only groups that meet specified conditions.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL HAVING: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2

SQL HAVING Full Explanation

HAVING is an optional clause used with GROUP BY to filter the result set of aggregated queries. WHERE filters individual rows before grouping, while HAVING filters entire groups created by GROUP BY. Because it operates on groups, HAVING can reference aggregate functions such as COUNT, SUM, AVG, MIN, and MAX. If GROUP BY is omitted, HAVING treats the entire result set as a single group, effectively acting like WHERE on aggregates. Execution order matters: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Incorrect placement of filters can lead to unexpected results or performance issues. Some databases allow HAVING without GROUP BY but behavior may vary, so test queries in your target dialect.

SQL HAVING Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE row_condition
GROUP BY column1
HAVING aggregate_condition;

SQL HAVING Parameters

condition (mandatory) - Boolean expression that can include aggregate functions and column references present in the GROUP BY list.

Example Queries Using SQL HAVING

--Return departments with more than 10 employees
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

--Find products with total sales above $50,000 this year
SELECT product_id, SUM(total_price) AS total_sales
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY product_id
HAVING SUM(total_price) > 50000;

--Identify cities with average order value below $20
SELECT city, AVG(total_price) AS avg_order
FROM orders
GROUP BY city
HAVING AVG(total_price) < 20;

Expected Output Using SQL HAVING

Each query returns only the grouped rows that satisfy the HAVING condition, excluding all other groups from the final result set

Use Cases with SQL HAVING

Enforce business rules on aggregated data (e.g., only show high revenue customers)
Clean up reports by hiding low-volume or insignificant groups
Support compliance checks requiring threshold-based summaries
Combine with GROUPING SETS, ROLLUP, or CUBE to filter complex aggregations

Common Mistakes with SQL HAVING

Using HAVING instead of WHERE for non-aggregated filters, causing unnecessary grouping
Referencing columns in HAVING that are neither aggregated nor included in GROUP BY (disallowed in strict SQL modes)
Expecting HAVING to improve performance when the filter could be pushed to WHERE
Forgetting that NULL values in aggregates may alter comparison results

First Introduced In

SQL-92

Frequently Asked Questions

What is the difference between HAVING and WHERE?

WHERE filters rows before grouping, while HAVING filters groups after aggregation and can reference aggregate functions.

Can I use HAVING without a GROUP BY clause?

Yes. The database treats the entire result set as a single group, so aggregate functions still work. Behavior is portable across major vendors but verify performance.

Are columns in HAVING required to appear in GROUP BY?

If a column is referenced without an aggregate function, it must also be listed in GROUP BY. Aggregated columns are exempt.

How can I optimize HAVING performance?

Move non-aggregate filters to WHERE, add indexes on grouping keys, and avoid complex expressions inside aggregate functions where possible.