select result.hospcode ,chospital_amp.hosname ,result.work_56 ,result.work_57 ,result.work_58 ,result.work_59 ,result.work_60 ,result.work_56 + result.work_57 + result.work_58 + result.work_59 + result.work_60 work_sum ,result.target from ( select hospcode ,count(case when (PAP56 = 1 or SPP56 = 1) then 1 end ) work_56 ,count(case when ((PAP56 = 0 and SPP56 = 0) and (PAP57 = 1 or SPP57 = 1)) then 1 end ) work_57 ,count(case when ((PAP56 = 0 and SPP56 = 0 and PAP57 = 0 and SPP57 = 0) and (PAP58 = 1 or SPP58 = 1)) then 1 end ) work_58 ,count(case when ((PAP56 = 0 and SPP56 = 0 and PAP57 = 0 and SPP57 = 0 and PAP58 = 0 and SPP58 = 0) and (PAP59 = 1 or SPP59 = 1)) then 1 end ) work_59 ,count(case when ((PAP56 = 0 and SPP56 = 0 and PAP57 = 0 and SPP57 = 0 and PAP58 = 0 and SPP58 = 0 and PAP59 = 0 and SPP59 = 0) and (PAP60 = 1 or SPP60 = 1)) then 1 end ) work_60 ,count(distinct cid) target from nhso_tmpqof004_person group by hospcode ) result INNER JOIN chospital_amp on chospital_amp.hoscode = result.hospcode