If you have been writing SQL queries for some time, then you may be very familiar with WHERE clauses. Although it has no impact on aggregated fields, there is a method to filter records based on aggregated values, which is to use the HAVING clause. This blog will introduce its working principle and provide several examples of using it in SELECT queries.

Aggregation and Having clauses

Aggregation is usually used in conjunction with grouping. In SQL, the GROUP BY clause can be used to implement it. By aggregating and grouping, we can gain a deeper understanding of the data. For example, an e-commerce company may want to track sales during a specific time period.

In many cases, we may not want to apply the GROUP BY clause on the entire dataset. In this case, we can use the GROUP BY command and conditional HAVING clause to filter out unwanted results. Similar to the WHERE clause, HAVING specifies one or more filtering conditions, but for a group or an aggregation. Therefore, HAVING is always placed after the WHERE and GROUP BY clauses, and before the (optional) ORDER BY clause:

Some examples

To better understand how HAVING works, let's run several SELECT queries using the Sakila sample database.

Our first query lists the people who have rented the most movies, sorted in descending order, so that the person who has rented the most movies ranks first. We will use the HAVING clause to remove customers who have rented less than 3 times, in order to shorten the list to some extent:

The following is the first page of the query and its results in Navicat Premium:

From these rental figures, we can further reduce the length of the list!

Filter rows through WHERE and HAVING

Just as GROUP BY and ORDER BY are applied to different stages of the query process, the same applies to WHERE and HAVING. Therefore, we can add these two clauses before and after grouping and aggregation to filter the results. For example, we can add a WHERE clause to limit the results to the first half of a given year:

The following is the first page of the above query and its results running in Navicat Premium:

Combine multiple conditions

Just as the WHERE clause supports multiple conditions using AND and OR keywords, the HAVING clause is no exception. For example, we can modify the HAVING clause to something similar to the following to find customers with rental numbers within a given range:

HAVING total_rentals >= 3 AND total_rentals <= 10