Figure out the total number of people in an overlapping er database

user_10372932 0
Viewed 46

I am trying to find:

  • the total number of doctors which aren't patients
  • the total number of patients which aren't doctors
  • the total number of people who are both patients and doctors

I can't seem to get the correct answer.

SQL:

CREATE TABLE persons (
  id integer primary key,
  name text
);

CREATE TABLE doctors (
  id integer primary key,
  type text,
  FOREIGN KEY (id) REFERENCES persons(id)
);

CREATE TABLE patients (
  id integer primary key,
  suffering_from text,
  FOREIGN KEY (id) REFERENCES persons(id)
);

INSERT INTO persons (id, name) VALUES
(1, 'bob'), (2, 'james'), (3, 'bill'), (4, 'mark'), (5, 'chloe');

INSERT INTO doctors (id, type) VALUES
(2, 'family doctor'), (3, 'eye doctor'), (5, 'family doctor');

INSERT INTO patients (id, suffering_from) VALUES
(1, 'flu'), (2, 'diabetes');

Select statement:

select count(d.id) as total_doctors, count(pa.id) as total_patients, count(d.id) + count(pa.id) as both_doctor_and_patient
from persons p
JOIN doctors d
ON p.id = d.id
JOIN patients pa
ON p.id = pa.id;

http://www.sqlfiddle.com/#!17/98ae9/2

aggregate-functions left-join count postgresql sql
3 Answers

One option uses left joins from persons and conditional aggrgation:

select 
    count(dr.id) filter(where pa.id is null) cnt_doctor_not_patient,
    count(pa.id) filter(where dr.id is null) cnt_patient_not_doctor,
    count(pa.id) filter(where do.id is not null) cnt_patient_and_doctor,
    count(*)     filter(where dr.id is null and pa.id is null) cnt_persons_not_dotor_nor_patient
from persons pe
left join doctors  dr on dr.id = pe.id
left join patients pa on pa.id = pe.id

As a bonus, this gives you an opportunity to count the persons that are neither patient nor doctor. If you don't need that information, then a full join is simpler, and does not require bringing the persons table:

select 
    count(dr.id) filter(where pa.id is null) cnt_doctor_not_patient,
    count(pa.id) filter(where dr.id is null) cnt_patient_not_doctor,
    count(pa.id) filter(where dr.id is not null) cnt_patient_and_doctor
from doctors dr 
full join patients pa using (id)
user_106767160

You can simply solve this using LEFT JOIN like:

--Aren't doctors:
SELECT count(*) from persons as A left join doctors as B on A.id=B.id where B.id is null

--Aren't patients:
SELECT count(*) from persons as A left join patients as B on A.id=B.id where B.id is null

--Both:
SELECT
   (SELECT count(*) from persons as A left join patients as B on A.id=B.id where B.id is not null) + 
   (SELECT count(*) from persons as A left join doctors as B on A.id=B.id where B.id is not null) 
AS summ
user_121939520

Here a CTE alternative:

 with doc_not_pat
as(
    select count(*) as Doc_Not_Pat
    from doctors d
    where not exists (select 1 from patients p where p.id = d.id)
),
pat_not_doc as(
   select count(*)  as Pat_Not_Doc
   from patients p
   where not exists ( select 1 from doctors d where d.id = p.id)
),
pat_and_doc as(
   select count(*) as Pat_And_Doc
   from patients p
   where exists (select 1 from doctors d where d.id = p.id)
)
select (select Doc_Not_Pat
        from doc_not_pat dcp) as Doc_Not_Pat,
       (select Pat_Not_Doc
       from pat_not_doc) as Pat_Not_Doc, 
       (select Pat_And_Doc
       from pat_and_doc) as  Pat_And_Doc
user_113012200
Related