How to include "zero" / "0" results in COUNT aggregate?

Question

I've just got myself a little bit stuck with some SQL. I don't think I can phrase the question brilliantly - so let me show you.

I have two tables, one called person, one called appointment. I'm trying to return the number of appointments a person has (including if they have zero). Appointment contains the person_id and there is a person_id per appointment. So COUNT(person_id) is a sensible approach.

The query:

SELECT person_id, COUNT(person_id) AS "number_of_appointments" 
FROM appointment 
GROUP BY person_id;

Will return correctly, the number of appointments a person_id has. However, a person who has 0 appointments isn't returned (obviously as they are not in that table).

Tweaking the statement to take person_id from the person table gives me something like:

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM appointment
JOIN person ON person.person_id = appointment.person_id
GROUP BY person.person_id;

This however, will still only return a person_id who has an appointment and not what I want which is a return with persons who have 0 appointments!

Any suggestions please?

Answer 1

You want an outer join for this (and you need to use person as the "driving" table)

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
  LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

The reason why this is working, is that the outer (left) join will return NULL for those persons that do not have an appointment. The aggregate function count() will not count NULL values and thus you'll get a zero.

If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null