SQL HAVING Clause: Syntax, Usage, and Examples
The SQL HAVING clause filters the results of a GROUP BY statement based on aggregated values. While WHERE filters rows before grouping, HAVING filters groups after aggregation. It’s an essential tool when you want to apply conditions to grouped data.
How to Use the SQL HAVING Clause
You use the SQL HAVING clause in queries that use GROUP BY. It works with aggregate functions like SUM(), AVG(), COUNT(), and MAX().
Syntax
sql
SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1
HAVING AGG_FUNC(column2) operator value;
AGG_FUNCis an aggregate function likeCOUNT,SUM, orAVG.operatoris a condition such as=,>,<,!=, etc.
Example
sql
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
This query returns only those departments that have more than 10 employees.
When to Use the HAVING Clause in SQL
Use the SQL HAVING clause when you want to filter aggregated values after grouping. It’s especially useful in reports, dashboards, or whenever you need to compare group-level metrics.
Use Case 1: Filter Based on Aggregated Values
You can filter out groups that don’t meet a certain threshold.
sql
SELECT category, SUM(sales) AS total_sales
FROM products
GROUP BY category
HAVING SUM(sales) > 10000;
This helps you focus only on high-performing categories.
Use Case 2: Find Duplicates
You can use HAVING with COUNT to detect duplicates in a table.
sql
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
This query shows emails that appear more than once—great for finding duplicates.
Use Case 3: Filter Aggregates in Joins
Pair HAVING with joins and aggregates to filter complex data.
sql
SELECT customers.name, SUM(orders.amount) AS total_spent
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name
HAVING SUM(orders.amount) > 500;
Only customers who spent more than $500 show up.
Examples of the SQL HAVING Clause in Action
Let’s explore common use cases for the HAVING clause SQL developers often encounter.
Example 1: Using COUNT()
sql
SELECT role, COUNT(*) AS role_count
FROM employees
GROUP BY role
HAVING COUNT(*) >= 3;
This filters out roles that have fewer than three people.
Example 2: Using SUM()
sql
SELECT region, SUM(sales) AS total_sales
FROM orders
GROUP BY region
HAVING SUM(sales) BETWEEN 5000 AND 20000;
It’s helpful when you want to focus on medium-performing regions.
Example 3: Using AVG()
sql
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
This shows which departments pay above a certain threshold.
Example 4: Multiple Conditions
sql
SELECT manager_id, COUNT(*) AS team_size, AVG(rating) AS avg_rating
FROM performance_reviews
GROUP BY manager_id
HAVING COUNT(*) > 5 AND AVG(rating) > 4;
You can stack conditions just like in a WHERE clause.
Example 5: Combine HAVING with ORDER BY
sql
SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id
HAVING SUM(quantity) > 100
ORDER BY total_quantity DESC;
You’re filtering and then sorting by the quantity sold.
Learn More About the SQL HAVING Clause
HAVING vs. WHERE in SQL
Many beginners confuse the two. Here’s the difference:
- Use
WHEREto filter rows before grouping. - Use
HAVINGto filter groups after aggregation.
sql
-- WHERE filters raw data
SELECT *
FROM orders
WHERE order_date >= '2024-01-01';
-- HAVING filters grouped results
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
In short: if you’re using an aggregate function in the condition, use HAVING.
SQL Query with HAVING Clause and GROUP BY
Sometimes you need both WHERE and HAVING in the same query:
sql
SELECT product_id, SUM(quantity) AS total_sold
FROM order_items
WHERE order_date >= '2024-01-01'
GROUP BY product_id
HAVING SUM(quantity) > 200;
This filters individual orders first (WHERE) and then filters the group totals (HAVING).
SQL HAVING Clause Without GROUP BY
Although rare, some databases allow HAVING without GROUP BY if you’re using an aggregate function across the whole table:
sql
SELECT SUM(amount) AS total_amount
FROM payments
HAVING SUM(amount) > 100000;
This filters based on the total across all records.
SQL Query HAVING Clause with Subqueries
You can nest HAVING inside subqueries or use it with derived tables:
sql
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > (
SELECT AVG(salary) FROM employees
);
Now you’re comparing each department’s average salary to the global average.
SQL Query with HAVING Clause for Conditional Aggregation
You can use conditional logic in aggregation, like CASE WHEN, inside your HAVING clause.
sql
SELECT customer_id,
SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered_orders
FROM orders
GROUP BY customer_id
HAVING SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) > 3;
This filters customers with more than 3 delivered orders.
The SQL HAVING clause gives you the power to filter groups based on summary values. You’ll use it anytime you work with GROUP BY and need to limit your results to specific criteria.