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.