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

Solution 1:

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