I'm trying to come up with a report that will give me a few variables, I need to know the practice, the amount of patients, and the amount of new patients related to the practice in a given month. Later on I will need to add additional variables

I'm trying to use the following query:

select practice, count(distinct appointment_id), 
(select count(distinct appointment_id) from Appointments 
  where Patient_Status = 'New Patient') 
from Appointments 
where ServiceDate between '2022-10-01' and '2022-10-31' 
group by practice 

Doing that gives me the following results:

column C (new patients) should be a lot smaller than column B (total patients) for each row, i.e. for the Austin row I would expect a value of 600-700 - but it seems like it's returning a really high aggregate value on each row.

What am I doing wrong? I'm fairly novice in SQL so would really appreciate the help

CodePudding user response：

Your group by will not affect your subquery either add a group by to your subquery as

select practice, count(distinct appointment_id), 
(select count(distinct appointment_id) from Appointments 
  where Patient_Status = 'New Patient'
  group by practice) 
from Appointments 
where ServiceDate between '2022-10-01' and '2022-10-31' 
group by practice

or duplicate your appointment table like

select practice, count(distinct A.appointment_id), count(distinct B.appointment_id) 
  from Appointments A, Appointments B
  where B.Patient_Status = 'New Patient'
  and A.ServiceDate between '2022-10-01' and '2022-10-31'
  and A.practice = B.practice
group by practice

The second approach is easier because in many cases you will also need to join your subquery and your main query

CodePudding user response：

Probably you just need to conditionally count the appointments, you don't need a sub-query for this just a case expression, does this give your expected results?

select practice, 
  count(distinct appointment_id) as Appointments,
  count(distinct case when Patient_Status = 'New Patient' then appointment_id end) as NewPatientAppointments
from Appointments 
where ServiceDate between '2022-10-01' and '2022-10-31' 
group by practice;