SQL ‘Having’ Clause
The ‘where’ clause, discussed previously, adds a condition to an SQL statement to limit the data returned. The limitation with this clause is that it cannot incorporate functions such as MIN, MAX, COUNT, SUM and AVG, into the conditions. This is where the ‘having’ clause comes in.
SELECT column_name1, column_name2, FUNCTION(column_name3) FROM table_name WHERE condition GROUP BY column_name1, column_name2 HAVING condition;
As can be seen above, the ‘having’ clause can be used in addition to and not instead of the ‘where’ clause.
With the ‘person‘ and ‘person_order‘ tables, used previously, a ‘having’ clause can be added to extend the final SQL statement discussed in the ‘group by’ section.
person | ||||
id | firstname | lastname | title | dob |
1 | Fred | Bloggs | Mr | 1980-05-05 |
2 | Simon | Smith | Mr | 1960-04-01 |
3 | Freida | Bloggs | Mrs | 1970-10-12 |
4 | Fiona | Jones | Miss | 1985-05-19 |
person_order | |||
id | order_date | order_total | person_id |
1 | 2016-01-19 | 101.60 | 1 |
2 | 2016-02-02 | 256.54 | 2 |
3 | 2016-03-05 | 222.10 | 4 |
4 | 2016-05-23 | 12.45 | 1 |
5 | 2016-07-29 | 325.65 | 2 |
A ‘having’ clause has been added below to limit the results returned to people who have made more than one order.
SELECT person.id, person.firstname, person.lastname, COUNT(*) AS 'count', MAX(person_order.order_total) AS 'max', SUM(person_order.order_total) AS 'sum' FROM person INNER JOIN person_order ON person.id=person_order.person_id GROUP BY person.id, person.firstname, person.lastname HAVING COUNT(*) > 1 ORDER BY person.id;
The results returned are as follows.
id | firstname | lastname | count | max | sum |
1 | Fred | Bloggs | 2 | 101.60 | 114.05 |
2 | Simon | Smith | 2 | 325.65 | 582.19 |
As with the ‘where’ clause, multiple conditions can be incorporated into the ‘having’ clause.
SELECT person.id, person.firstname, person.lastname, COUNT(*) AS 'count', MAX(person_order.order_total) AS 'max', SUM(person_order.order_total) AS 'sum' FROM person INNER JOIN person_order ON person.id=person_order.person_id GROUP BY person.id, person.firstname, person.lastname HAVING COUNT(*) > 1 AND MAX(person_order.order_total) < 200 ORDER BY person.id;
Here, both an order count of greater than one is specified, together with a maximum order total of less than 200. This results in only one record being returned.
id | firstname | lastname | count | max | sum |
1 | Fred | Bloggs | 2 | 101.60 | 114.05 |