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

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?

USE join to get 0 count in the result using GROUP BY.

simply 'join' does Inner join in MS SQL so , Go for left or right join.

If the table which contains the primary key is mentioned first in the QUERY then use LEFT join else RIGHT join.

EG:

select WARDNO,count(WARDCODE) from MAIPADH 
right join  MSWARDH on MSWARDH.WARDNO= MAIPADH.WARDCODE
group by WARDNO

.

select WARDNO,count(WARDCODE) from MSWARDH
left join  MAIPADH on MSWARDH.WARDNO= MAIPADH.WARDCODE group by WARDNO

Take group by from the table which has Primary key and count from the another table which has actual entries/details.

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_id
GROUP BY person.person_id;

The problem with a LEFT JOIN is that if there are no appointments, it will still return one row with a null, which when aggregated by COUNT will become 1, and it will appear that the person has one appointment when actually they have none. I think this will give the correct results:

SELECT person.person_id,
(SELECT COUNT(*) FROM appointment WHERE person.person_id = appointment.person_id) AS 'Appointments'
FROM person;

To change even less on your original query, you can turn your join into a RIGHT join

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

This just builds on the selected answer, but as the outer join is in the RIGHT direction, only one word needs to be added and less changes. - Just remember that it's there and can sometimes make queries more readable and require less rebuilding.

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_id
FROM person) P
LEFT JOIN 
(select person_id, count(*) as nb_apptmts
from appointment 
group by person_id) A
ON P.person_id = A.person_id