HAVING
Similar to the way in which you can filter rows using the WHERE statement, HAVING can filter groups. While WHERE can only affect a single record, HAVING focuses on a group as a whole. If you want to receive a list of those countries that have produced two or more creators, you can not make that selection on the basis of one single row. To be able to see if there is another row with the same value, you need to take the entire table into account, and all the records in this table needs to be divided into groups.
In the HAVING clause, you can use the same conditions as in the WHERE clause. Unlike the WHERE clause, however, you can also use one of the aggregate functions after HAVING.
The syntax of the HAVING clause is as follows:
SELECT [name of column]
FROM [name of table]
GROUP BY [name of column]
HAVING [condition]
If we want to compile a list of those countries that have produced two or more creators, the following SQL query can be used:
SELECT COUNTRY_BORN
FROM CREATOR
GROUP BY COUNTRY_BORN
HAVING COUNT(*) >= 2 ;
Exercises
- Select those libraries that are represented by less than three treasures
- Select those subjects that have been assigned to two or more treasures in the database