Linux Hint Posted March 14 Share Posted March 14 Structured Query Language, SQL, or however you want to pronounce it, is the foundation of querying and interacting for relational databases. How you use your SQL database heavily depends on your application requirements. 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: SELECT column1, column2, aggregate_function(column) AS alias 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: SELECT 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: name |total_films| -----------+-----------+ 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: SELECT 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. View the full article Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.