select hospcode ,hosname ,count(case when DATE_SERV between "20171001" and "20171231" and USEDRUG = 1 then pid end) work_t3 ,count(case when DATE_SERV between "20180101" and "20180331" and USEDRUG = 1 then pid end) work_t4 ,count(case when DATE_SERV between "20180401" and "20180630" and USEDRUG = 1 then pid end) work_t1 ,count(case when DATE_SERV between "20180701" and "20180930" and USEDRUG = 1 then pid end) work_t2 ,count(case when DATE_SERV between "20171001" and "20180930" and USEDRUG = 1 then pid end) sum_work ,count( pid) target from nhso_tmpqof0051_diag61 LEFT JOIN chospital_amp on chospital_amp.hoscode = nhso_tmpqof0051_diag61.HOSPCODE group by hospcode