SQL Server SQL -尝试在分组依据中包含子查询

0dxa2lsx  于 2022-12-22  发布在  其他
关注(0)|答案(2)|浏览(138)

我正在尝试编写一份报告,其中包含几个变量,我需要了解某个月内的实践、患者数量以及与实践相关的新患者数量。稍后,我将需要添加其他变量
我正在尝试使用以下查询:

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

这样做会得到以下结果:
| 实践|B栏|C栏|
| - ------| - ------| - ------|
| 奥斯汀|小行星7150|小行星556979|
| 休斯敦|小行星6175|小行星556979|
| 达拉斯|小行星8522|小行星556979|
对于每一行,列C(新患者)应该比列B(患者总数)小得多,即对于Austin行,我期望值为600 - 700-但它似乎在每一行上返回了一个非常高的聚合值。
我做错了什么?我是SQL的新手,所以非常感谢你的帮助

wvyml7n5

wvyml7n51#

您的分组依据不会影响子查询,也不会将分组依据添加到子查询中

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

或复制约会表,如

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

第二种方法更简单,因为在许多情况下,您还需要连接子查询和主查询

xzlaal3s

xzlaal3s2#

也许你只需要有条件地计算约会,你不需要一个子查询,这只是一个 *case表达式 *,这能给予你期望的结果吗?

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;

相关问题