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