Joe Celko's SQL for Smarties, 3rd Edition

20.2. GROUP BY and HAVING

One of the biggest problems in working with the GROUP BY clause lies in not understanding how the WHERE and HAVING clauses work. Consider this query to find all departments with fewer than five programmers:

SELECT dept_nbr
  FROM Personnel
 WHERE job_title = 'Programmer'
 GROUP BY dept_nbr
HAVING COUNT(*) < 5;

The result of this query does not have a row for any departments with no programmers. The order of execution of the clauses does WHERE first, so those employees whose jobs are not equal to 'Programmer' are never passed to the GROUP BY clause. You have missed data that you might want to trap.

The next query will also pick up those departments that have no programmers, because the COUNT(DISTINCT x) function will return ...