Just as we use the WHERE clause to limit the records returned, we can limit the groups returned, if they don’t meet a specific criteria, using the keyword HAVING. HAVING will not remove rows from being eligible in our result; however, it will remove groups that don’t meet the criteria we set.

As we continue to build on the number of owners from around the world, let’s say we are only interested in seeing countries that have more than 10 pet owners.

SELECT CountryOfResidence, COUNT(*) AS NumberOfOwners
 FROM PetOwner
 GROUP BY CountryOfResidence
 HAVING COUNT(*) > 10
 ORDER BY COUNT(*) DESC;
OUTPUT

CountryOfResidence             NumberOfOwners
——————————-        ———————-
United States Of America         57
Great Britain                               48
Costa Rica                                   33
Singapore                                    31
Tanzania                                      23
Argentina                                     21
New Zealand                               20
Peru                                             18
Luxembourg                               18
Kenya                                           16
Israel                                            14
Jamaica                                        12
El Salvador                                  12

(13 row(s) affected)

In this instance, the list will reduce by about half as we don’t have enough records from many of the countries that qualify with at least 10 owners. The HAVING COUNT(*) > 10 component of our query will ensure there is no record in the NumberOfOwners column that is less than 10.

Just a reminder that if we wanted to include countries with exactly 10 owners we would have to use the >= comparison.

We can also join tables together and still use grouping. For example, if we wanted to get the most popular dog names by country, our query would look like this. I use the HAVING clause to eliminate some results for display purposes.

SELECT NameOfDog, PetOwner.CountryOfResidence, COUNT(*) AS NumberOfOwners
 FROM PetOwner
 INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
 GROUP BY NameOfDog, PetOwner.CountryOfResidence
 HAVING COUNT(*) > 3
 ORDER BY COUNT(*) Desc;

OUTPUT

NameOfDog   CountryOfResidence          NumberOfOwners
———-             —————————             ————–
Hercules        United States Of America       8
Pongo             United States of America       5
Spike              United States Of America       5
Spike              New Zealand                            5
Spike              Peru                                           4
Clifford           Singapore                                 4
Ace                 Great Britain                             4
Butch             Great Britain                             4
Hercules        Great Britain                             4
Hercules        Luxembourg                             4

(10 row(s) affected)

**Warning** I want to make another point of mentioning how the filter process works. A WHERE clause will either exclude rows or specify which row can be included. A HAVING clause will look at groups and then make a filter on the group. In our last example, Spike had the highest name count for the New Zealand. If we were to exclude New Zealand and the name of Spike, our results would be much different. I again use the having clause to limit the number of records returned.

SELECT NameOfDog, PetOwner.CountryOfResidence, COUNT(*)
 FROM PetOwner
 INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
 WHERE CountryOfResidence <> 'New Zealand' AND NameOfDog <> 'Spike'
 GROUP BY NameOfDog, PetOwner.CountryOfResidence
 HAVING COUNT(*) > 3
 ORDER BY COUNT(*) Desc;

OUTPUT
NameOfDog   CountryOfResidence    NumberOfOwners
———-           ————————-      ————–
Hercules         United States Of America    8
Pongo             United States of America     5
Spike              United States Of America     5
Spike              New Zealand                          5
Spike              Peru                                         4
Clifford           Singapore                               4
Hercules        Luxembourg                           4

(7 row(s) affected)