select hospcode ,hosname ,count(case when DATE_SERV between "20181001" and "20181231" and USEDRUG = 1 then pid end) work_t3 ,count(case when DATE_SERV between "20190101" and "20190331" and USEDRUG = 1 then pid end) work_t4 ,count(case when DATE_SERV between "20190401" and "20190630" and USEDRUG = 1 then pid end) work_t1 ,count(case when DATE_SERV between "20190701" and "20190930" and USEDRUG = 1 then pid end) work_t2 ,count(case when DATE_SERV between "20181001" and "20190930" and USEDRUG = 1 then pid end) sum_work ,count( pid) target from nhso_tmpqof0051_diag60 LEFT JOIN chospital_amp on chospital_amp.hoscode = nhso_tmpqof0051_diag60.HOSPCODE group by hospcode