However, one thing that does not change is the need to filter out the data for matching results. One common task is aggregating and filtering the results based on a specific condition. SQL knows this and provides us with the HAVING clause and the COUNT function that can help in such tasks.
In this guide, we will walk you through how you can combine the HAVING clause in SQL and the COUNT() function.
SQL HAVING Clause and COUNT() Function
In SQL, we use the HAVING clause to filter the results of a GROUP BY query based on a specific condition. We mainly use it in conjunction with aggregate functions like COUNT, SUM, AVG, and MAX to filter the groups of rows that meet a specific criterion.
The COUNT function, on the other hand, allows us to count the number of rows in a group.
Syntax:
The following expresses the syntax of the HAVING clause in conjunction with the COUNT() function:
FROM table
GROUP BY column1, column2
HAVING aggregate_function(column) operator value;
In the given example, we use the HAVING clause to apply a condition to filter the groups. We then specify the aggregate function which, in this case, is the count() function with the column on which we wish to filter.
Examples:
Let us explore some practical examples on how to use the HAVING clause in conjunction with the COUNT() function.
For demonstration purposes, we use the Sakila sample database which is freely available to download and use for both MySQL and PostgreSQL.
Feel free to use any dataset that you deem applicable in this context.
Example 1:
Let us assume a scenario where we need to find the film categories that contain more than 10 films. We can use the HAVING COUNT clause as shown in the following:
category.name,
COUNT(film.film_id) AS total_films
FROM
category
JOIN film_category ON
category.category_id = film_category.category_id
JOIN film ON
film_category.film_id = film.film_id
GROUP BY
category.name
HAVING
COUNT(film.film_id) > 10;
In the given example query, we start by selecting the category name and then count the number of films in each category using the COUNT() function.
We then use the GROUP BY clause to group the results based on the category name.
Finally, we use the HAVING clause to filter out the categories with a film count greater than 10.
The resulting table is as follows:
-----------+-----------+
Action | 64|
Animation | 66|
Children | 60|
Classics | 57|
Comedy | 58|
Documentary| 68|
Drama | 62|
Family | 69|
Foreign | 73|
Games | 61|
Horror | 56|
Music | 51|
New | 63|
Sci-Fi | 61|
Sports | 74|
Travel | 57|
This should show the number of films in each category.
Example 2:
Suppose we want to find the actors who have not appeared in any film. We can use a left join and the HAVING COUNT clause as follows:
actor.actor_id,
actor.first_name,
actor.last_name
FROM
actor
LEFT JOIN film_actor ON
actor.actor_id = film_actor.actor_id
GROUP BY
actor.actor_id,
actor.first_name,
actor.last_name
HAVING
COUNT(film_actor.actor_id) = 0;
This query filters out the results where the actor has no film in the database.
Conclusion
In this post, you learned how to use the HAVING clause in conjunction with the SQL aggregate function which is COUNT() to filter out the results that match a specific condition. We use various Boolean operators such as greater than, less than, etc. to specify various conditions.
