组内的couting组合 [英] Couting combinations within a group

问题描述

我需要一种方式来计算患者的代码组合，所以我需要知道基于每个服务程序协议的总持续时间，因此基于下面的图片我需要它像这样

I need a way way to count combinations of code by patients, so I need to know based on this report total duration of each Service Program Protocol, so based on the picture below I need it to be like this

病人：00000036

Adult CSS IND 240

Adult CSS IND 240

成人OP IND 120

Adult OP IND 120

病人：00000040

CSS IND 420

Adult CSS IND 420

成人OP IND 60

Adult OP IND 60

我使用的是Microsoft SQL Server 2005，在SQL中，但如果它可以在水晶中完成我可以工作。先谢谢你。

I am using Microsoft SQL Server 2005 and I would prefer a fix in SQL, but if it could be done in crystal I could work with that. Thank you in advance.

select 

pct.patient_id,
pct.clinic_id,
pct.service_id,
pct.program_id,
pct.protocol_id,
pct.proc_duration


from patient_clin_tran pct
join patient p
on pct.patient_id = p.patient_id and pct.episode_id = p.episode_id
join patient_custom pc
on pct.patient_id = pc.patient_id
join staff s
on pct.attending_id = s.staff_id
where pc.health_home = 'Y'
group by pct.patient_id, pct.clinic_id, pct.service_id, pct.program_id, pct.protocol_id, pct.proc_duration
order by pct.patient_id, pct.clinic_id, pct.service_id, pct.protocol_id

推荐答案

你很近。您需要 GROUP BY ，但不希望 GROUP BY 持续时间列。相反，您希望使用 proc_duration 中的 SELECT 列表中的 SUM c $ c> column：

You were close. You do need the GROUP BY, but you do not want to GROUP BY the duration column. Instead you want to use the SUM function in the SELECT list on your proc_duration column:

select 
   pct.patient_id,
   pct.clinic_id,
   pct.service_id,
   pct.program_id,
   pct.protocol_id,
   SUM(pct.proc_duration) AS [Total Duration]  

FROM patient_clin_tran pct
  join patient p
  on pct.patient_id = p.patient_id and pct.episode_id = p.episode_id

  join patient_custom pc
  on pct.patient_id = pc.patient_id

  join staff s
  on pct.attending_id = s.staff_id

where pc.health_home = 'Y'
group by pct.patient_id, pct.clinic_id, pct.service_id, pct.program_id, pct.protocol_id
order by pct.patient_id, pct.clinic_id, pct.service_id, pct.program_id, pct.protocol_id

这篇关于组内的couting组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！