If you have been writing SQL queries for some time, you are probably quite familiar with the WHERE clause. While it has no effect on aggregated fields, there is a way to filter records according to aggregate values, and that is by using the HAVING clause. This blog will cover how it works as well as provide a few examples on using it in SELECT queries.
Aggregation and the HAVING Clause
Aggregation is typically used in conjunction with grouping. In SQL, that's accomplished using the GROUP BY clause. Aggregation, together with grouping, allows us to glean high level insights into our data. For example, an eCommerce company might want to track sales over a given time period.
In many cases, we may not want to apply the GROUP BY clause on the entire dataset. In those instances, we can employ the GROUP BY command along with the conditional HAVING clause to filter out unwanted results. Similar to the WHERE clause, HAVING specifies one or more filter conditions, but for a group or an aggregation. As such, HAVING is always placed after the WHERE and GROUP BY clauses but before the (optional) ORDER BY clause:
SELECT column_list FROM table_name WHERE where_conditions GROUP BY column_list HAVING having_conditions ORDER BY order_expression
Some Practical Examples
To get a better idea on how HAVING works, let's run a few SELECT queries against the Sakila Sample Database.
Our first query lists our top movie renters, sorted in descending order, so that the person with the most rentals appears at the top. We'll use the HAVING clause to remove customers with less than three rentals in order to shorten the list somewhat:
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(r.rental_id) AS total_rentals
FROM
customer AS c
LEFT JOIN rental AS r ON c.customer_id = r.customer_id
GROUP BY c.customer_id
HAVING total_rentals >= 3
ORDER BY total_rentals DESC;
Here is the query and the first page of results in Navicat Premium:
Judging by those rental numbers, we could have narrowed down the list substantially more!
Filtering Rows Using Both WHERE and HAVING
Just as GROUP BY and ORDER BY are applied at different points in the querying process, so too are WHERE and HAVING. Hence, we can include both to filter results both before and after grouping and aggregation. For example, we can add a WHERE clause to restrict results to the first half of a given year:
SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(r.rental_id) AS total_rentals
FROM
customer AS c
LEFT JOIN rental AS r ON c.customer_id = r.customer_id
WHERE r.rental_date BETWEEN '2005-01-01' AND '2005-06-30'
GROUP BY c.customer_id
HAVING total_rentals >= 3
ORDER BY total_rentals DESC;
Once again, here is the above query and the first page of results in Navicat Premium:
Combining Multiple Conditions
Just as the WHERE clause supports multiple conditions using the AND and OR keywords, so too does HAVING. For example, we could find customers whose rental numbers fall within a given range by modifying the HAVING clause to something like the following:
HAVING total_rentals >= 3 AND total_rentals <= 10
Conclusion
In today's blog we learned how to filter grouped and aggregated fields using the HAVING clause.
Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!
