Selecting Particular Groups with HAVING

The next clause in the SELECT statement is HAVING. A GROUP BY with a HAVING clause is like a SELECT with a WHERE clause. For example:

select count(*), job
from employee
group by job
having count(*)=1;

This query will select the jobs in the company for which we have only one employee in the role. It should produce results similar to the following:

+----------+-----------------------+
| count(*) | job |
+----------+-----------------------+
| 1 | DBA |
| 1 | Systems Administrator |
+----------+-----------------------+
2 rows in set (0.05 sec)

It's been our experience that people who are just beginning to use SQL often get WHERE and HAVING confused. You will use WHERE in just about every query you write to test conditions that relate to individual rows. You will use HAVING when you want to apply a conditional to whole groups.