Having Clause in SQL
This tutorial aims to help you understand and use the HAVING clause in SQL statements.
Let us dive in.
SQL Having Clause
The HAVING clause in SQL allows you to set a condition in the groups defined in an SQL GROUP BY clause.
You can use the GROUP BY clause to organize the data into various partitions in SQL. You can perform multiple actions on the groups, such as aggregate functions.
Using the HAVING clause, you can specify a condition for the groups. However, you can use the HAVING clause without a GROUP BY pairing. In such a case, the HAVING clause will behave similarly to the WHERE clause which allows you to search for matching records.
The following code snippet defines the syntax for the SQL HAVING clause:
FROM table_name
GROUP BY group_by_clause
HAVING group_condition;
Example 1: Using the HAVING Clause with Film Table
To best understand how to use the HAVING clause in SQL, we will use a sample database provided by MySQL.
You can check out the following provided resource for more information:
https://dev.mysql.com/doc/index-other.html
For this illustration, we will use the film table from the sakila database which is provided in the given link.
We can find the films with with a rental rating of 2.99 and above using the HAVING clause as shown in the following query:
from film
group by rating
having rental_rate >= 2.99;
The resulting table is as follows:
In this case, the query finds 4 matching records as shown in the previous table.
Examlpe 2: Using the HAVING Clause with an Aggregate Function
We can also use the sum() function to determine the film with the sum of the film ratings with a specific range of rental_rate.
from film
group by rating
having sum(rental_rate) between 500 and 600;
In this case, the query should return the table as follows:
Source: linuxhint.com