尝试使用 count 语句对多个个体的多个值进行计数

Question

我在社区卫生中心工作。 我正在尝试计算我们的每个牙科提供商使用多个服务代码的次数。 尝试创建一个脚本，该脚本将返回类似于以下内容的内容：

provider|code|code|code etc...
---------------------------
name    | #  | #  | #
---------------------------
name    | #  | #  | #
---------------------------
name    | #  | #  | #
---------------------------
etc...  

我已经有一个脚本返回了我正在寻找的内容，但不是我需要的格式

select pm.description [Provider], p.service_item_id [Code], p.service_item_description [Service], COUNT(pm.description) As Count
from patient_procedure p 
inner join location_master l on p.location_id = l.location_id 
inner join provider_master pm on p.provider_id = pm.provider_id
inner join patient_encounter p2 on p.enc_id = p2.enc_id
where l.location_name like '%dental%'
and p2.enc_timestamp between '20190101 00:00:00.000' and '20190630 23:59:59.999'
and p2.billable_ind = y
group by p.service_item_id, p.service_item_description, pm.description
order by p.service_item_description ASCENDING

它返回：

Provider| Code| Service| Count

Answer 1

如果要为每个代码获取不同的计数，可以使用条件COUNT语句：

SUM(CASE WHEN service_item_id = 1 THEN 1 ELSE 0 END) AS count_id_1

在您的情况下，它看起来像这样：

SELECT 
pm.description [Provider], 
p.service_item_id [Code], 
p.service_item_description [Service], 
SUM(CASE WHEN p.service_item_id = 1 THEN 1 ELSE 0 END) As Count_id_1,
SUM(CASE WHEN p.service_item_id = 2 THEN 1 ELSE 0 END) As Count_id_2,
SUM(CASE WHEN p.service_item_id = 3 THEN 1 ELSE 0 END) As Count_id_3
FROM patient_procedure p 
INNER JOIN location_master l ON p.location_id = l.location_id 
INNER JOIN provider_master pm ON p.provider_id = pm.provider_id
INNER JOIN patient_encounter p2 ON p.enc_id = p2.enc_id
WHERE l.location_name LIKE '%dental%'
AND p2.enc_timestamp BETWEEN '20190101 00:00:00.000' AND '20190630 23:59:59.999'
AND p2.billable_ind = y
GROUP BY p.service_item_id, p.service_item_description, pm.description
ORDER BY p.service_item_description ASCENDING

这将为您提供要跟踪的任意数量的不同代码的COUNTs。