Grouping case when results

Question

I have the following query working.

select sh.encounter_id
                 , claims_total_allowable_cost
                 , (case when proc_code_desc = 'Inj Cefazolin Sodium, 500mg' then 1 else 0 end)   as Cefazolin
                 , (case when proc_code_desc = 'Inj Cefoxitin Sodium, 1 G' then 1 else 0 end)     as Cefoxitin
                 , (case when proc_code_desc = 'Inj Fentanyl Citrate' then 1 else 0 end)          as Fentanyl
                 , (case when proc_code_desc = 'Inj Hydromorphone, 4mg' then 1 else 0 end)        as Hydro
                 , (case when proc_code_desc = 'Inj Ketorolac Trometha, 15mg' then 1 else 0 end)  as Ketorolac
                 , (case when proc_code_desc = 'Inj, Ondansetron Hci, 1 Mg' then 1 else 0 end)    as Ondansetron
                 , (case when proc_code_desc = 'Inj, Propofol, 10 Mg' then 1 else 0 end)          as Propofol
                 , (case when proc_code_desc = 'Ringer*' then 1 else 0 end)                       as Ringers

            from health as sh

            where cohort_description = 'Lap '

              and sh.admit_dtm >= '2018-10-01'::date
              and sh.admit_dtm <= '2019-09-30'::date

I get a result like this one:

    encounter_id    claims_total_allowable_cost cefazolin   cefoxitin   fentanyl    hydro   ketorolac   

    121                  4200.85                   0            0           0         0         0   
    121                  4200.85                   0            0           0         0         0   
    121                  4200.85                   0            0           0         0         1   
    121                  4200.85                   0            0           0         1         0   

I would like a result like this:

    encounter_id    claims_total_allowable_cost cefazolin   cefoxitin   fentanyl    hydro   ketorolac   

    121                  4200.85                   0            0           0         1         1

Any help would be greatly appreciated!!

Answer 1

Just add group by and max() to your existing query

 Select column1, max(column2), 
   Max(column3) ...
  From (your existing query)
 Group by column1