Skip to main content

HAVING clause

The HAVING clause works in conjunction with the GROUP BY clause. That is, you cannot have a HAVING clause without a GROUP BY clause. Following calculation of the function it is possible to restrict the rows displayed.

For example, only display those sections that have a salary bill greater than 100,000:

SELECT section_name, SUM(salary) FROM employee

GROUP BY section_name

HAVING SUM(salary) > 100000;

Further examples are:

SQL command Result

SELECT date_of_birth, COUNT(*)

FROM employee

GROUP BY date_of_birth

HAVING COUNT(*) > 1;

Find out how many employees have date of births on the same day. Only show results where there are at least two

SELECT surname, COUNT(*)

FROM employee

GROUP BY surname

HAVING surname <> 'Smith';

Find how many employees have the same surname, excluding those named 'Smith'

See note below



Note: Some of you may suggest that this example could be written in a different way by using the WHERE clause, and you would be right:

SELECT surname, COUNT(*)

FROM employee

WHERE surname <> 'Smith'

GROUP BY surname;

Often, SQL commands can be written in different ways to get the same result. This is part of the fun of SQL.

Next: UPPER and LOWER clause