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

SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1
HAVING AGG_FUNC(column2) operator value;

• AGG_FUNC is an aggregate function like COUNT, SUM, or AVG.
• operator is a condition such as =, >, <, !=, etc.

Example

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.

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.

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.

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()

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()

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()

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

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

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 WHERE to filter rows before grouping.
• Use HAVING to filter groups after aggregation.

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

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:

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:

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.

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.