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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to postgresql

Examples related to aggregate-functions