How HAVING works in SQL? Best HAVING examples

When working with SQL queries, I often come across situations where I need to filter aggregated results. That’s where the HAVING clause comes into play. In this article, I’ll explain how HAVING works in SQL, provide examples, and compare it with the WHERE clause to ensure you get the best understanding of its usage.

Understanding the HAVING Clause

The HAVING clause in SQL is used to filter results after they have been grouped using the GROUP BY statement. Unlike the WHERE clause, which filters rows before aggregation, HAVING filters groups of data after aggregate functions have been applied.

Basic Syntax of HAVING

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING condition;

Here’s what each part does:

SELECT – Specifies the columns to retrieve.
FROM – Specifies the source table.
GROUP BY – Groups results based on a specified column.
HAVING – Filters the grouped results based on a condition.

HAVING vs WHERE: What’s the Difference?

Feature	WHERE	HAVING
Execution order	Filters rows before aggregation	Filters grouped results after aggregation
Usage with aggregate functions	Cannot use aggregate functions	Can use aggregate functions
Requires GROUP BY	No	Yes

Simple Example of HAVING

Let’s say I have a sales table with the following data:

CREATE TABLE Sales (
    id INT PRIMARY KEY,
    salesperson VARCHAR(100),
    amount DECIMAL(10,2)
);

INSERT INTO Sales (id, salesperson, amount) VALUES
(1, 'Alice', 500),
(2, 'Bob', 700),
(3, 'Alice', 300),
(4, 'Bob', 400),
(5, 'Charlie', 600);

If I want to find salespeople who have sold more than $500 in total, I can use:

SELECT salesperson, SUM(amount) AS total_sales
FROM Sales
GROUP BY salesperson
HAVING SUM(amount) > 500;

This returns:

salesperson	total_sales
Alice	800
Bob	1100

Using HAVING with Multiple Conditions

I can also use multiple conditions in a HAVING clause. If I want to find salespeople whose total sales exceed $500 but are less than $1000, I’d do:

SELECT salesperson, SUM(amount) AS total_sales
FROM Sales
GROUP BY salesperson
HAVING SUM(amount) > 500 AND SUM(amount) < 1000;

This would return only Alice because her total sales are within that range.

Combining HAVING with WHERE

To maximize query efficiency, I often use WHERE to filter rows before aggregation and HAVING to filter after aggregation. For example:

SELECT salesperson, SUM(amount) AS total_sales
FROM Sales
WHERE amount > 200  -- Filters individual sales greater than 200
GROUP BY salesperson
HAVING SUM(amount) > 500;

Here, WHERE ensures only sales over $200 are considered, then HAVING filters the grouped results.

Conclusion

The HAVING clause is essential for filtering aggregated data in SQL. By understanding the difference between WHERE and HAVING, I can write more efficient and meaningful queries. Whether filtering total sales, average scores, or any other aggregated data, HAVING is a powerful tool in SQL.

Other interesting article:

How ORDER BY works in SQL? Best ORDER BY examples