SQL Having Clause
In this guide, we will explore how we can use the HAVING clause in conjunction with GROUP BY and aggregate function in SQL.
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 will 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 contains 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 counting 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 that is 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 COUNT() aggregate function 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.
Source: linuxhint.com