The HAVING clause allows us to specify a search condition for a group or an aggregate. It is used with the GROUP BY clause that divides rows into groups. It functions similarly to the WHERE clause, which you are already familiar with. The WHERE clause filters individual rows based on a specified condition. The HAVING clause filters groups of rows based on a set of conditions. They are similar, but separate from one another.
Let's take a look at the invoice table to find the SUM of the total that is grouped by country:
SELECT billing_country, SUM(total)
FROM invoice
GROUP BY billing_country;
We could not use the WHERE clause in this case, as the WHERE clause only looks at individual rows and not groups of rows. If we wanted to list only the countries that had the sum of the total greater than 50, we would add the HAVING clause to compare the aggregate function after the GROUP BY clause. Again, using the WHERE clause would not work:
SELECT billing_country, SUM(total)
FROM invoice
WHERE total > 50
GROUP BY billing_country;
This would look at individual rows that had a total > 50 instead of the groups. Instead, our query should look like this:
SELECT billing_country, SUM(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50;
Note, too, that we don’t have to have the same aggregate functions listed in the SELECT clause and HAVING clause. For example, if we wanted to show the number of invoices having the total amount by country > 50, we can change the SUM to COUNT in the SELECT clause:
SELECT billing_country, COUNT(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50;
We could also sort the results using the aggregate function as well:
SELECT billing_country, COUNT(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50
ORDER BY SUM(total);
We can use a variety of aggregate functions like the COUNT function to help filter. For example, we may want to find the countries and the number of customers that have a count greater than five:
SELECT country, COUNT(*)
FROM customer
GROUP BY country
HAVING COUNT(*) > 5;
We could have multiple conditions to filter based on aggregate values by using the AND and OR operators in the HAVING clause. We may want to filter the group further to ensure that the number of customers also checks for the number of customers that have a fax count greater than two. Note that as a reminder, counting a specific column only counts the non-null values, so if there is no fax, the row isn’t counted – unlike using COUNT(*) where it counts the number of rows.
SELECT country, COUNT(*),COUNT(fax)
FROM customer
GROUP BY country
HAVING COUNT(*) > 5 AND COUNT(fax) > 2;
[MUSIC PLAYING] The HAVING clause is very similar to WHERE clause. The only difference is that the HAVING clause is based on the GROUP BY clause and the aggregate values associated with it, whereas the WHERE clause is based on individual rows. So in this case here, we're taking a look at our query from the last tutorial where we're looking at the number of countries and then the number of customers within each country individually.
So in this case here, if we're trying to add in the HAVING clause, we might want to select all the countries that have at least five customers associated with them. So to make that, we're going to extend on the query itself. We're going to say HAVING a count, star, where we have the aggregate function, greater than 5, or in this case here, greater or equal to 5.
And if we run this, it'll only display those countries, which in this case is four of them, that have the count of the number of customers five or greater than. It is important to note that we can actually make these changes and modifications a little bit different than what we typically would have by utilizing the HAVING clause for different parameters. Let's go ahead and take a look at the track table in this case here, where we might want to select the tracks based on the album.
So we're going to group them and find the number of tracks within a specific album as well as the costs associated with each one of those albums. So we're going to go ahead and do a SELECT the album ID, get the count, get the sum of the unit price from the track table, and then we're going to group based on the album ID. So in this case here, for every single different album in this case, we'll have these different options. And that's going to be displayed.
Some of them have one single track per album that's going to be listed. Some of them have many more associated with them. Now if we want to utilizing the HAVING clause, we can select all the ones that have more than one track associated with them. And that'll drop our selections down. We could also have it such that we're going to check based on the sum of the price. We could have multiple different options. So in this case here, if we want to select a count being more than 1. But we also want the sum of the unit price being greater than 10.
We should see this one here disappear, as both parameters are going to be checked in this case here. What you'll notice, that it includes all the different options within the HAVING clause, as you would typically see with the WHERE clause. The only difference in this case here is that we're utilizing the aggregate functions rather than utilizing individual column names.
[MUSIC PLAYING]
Source: Authored by Vincent Tran
