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

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_idGROUP 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

You must use LEFT JOIN instead of INNER JOIN

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

if you do the outer join (with the count), and then use this result as a sub-table, you can get 0 as expected (thanks to the nvl function)

Ex:

select P.person_id, nvl(A.nb_apptmts, 0) from (SELECT person.person_idFROM person) PLEFT JOIN (select person_id, count(*) as nb_apptmtsfrom appointment group by person_id) AON P.person_id = A.person_id