select hospcode ,hosname ,count(case when DATE_SERV between "20170401" and "20170630" and USEDRUG = 1 then pid end) work_t3 ,count(case when DATE_SERV between "20170701" and "20170930" and USEDRUG = 1 then pid end) work_t4 ,count(case when DATE_SERV between "20171001" and "20171231" and USEDRUG = 1 then pid end) work_t1 ,count(case when DATE_SERV between "20180101" and "20180331" and USEDRUG = 1 then pid end) work_t2 ,count(case when DATE_SERV between "20170401" and "20180331" and USEDRUG = 1 then pid end) sum_work ,count( pid) target from nhso_tmpqof0051_diag LEFT JOIN chospital_amp on chospital_amp.hoscode = nhso_tmpqof0051_diag.HOSPCODE group by hospcode