select hospcode ,hosname ,count(case when DATE_SERV between "20161001" and "20161231" and USEDRUG = 1 then pid end) work_t3 ,count(case when DATE_SERV between "20170101" and "20170331" and USEDRUG = 1 then pid end) work_t4 ,count(case when DATE_SERV between "20170401" and "20170630" and USEDRUG = 1 then pid end) work_t1 ,count(case when DATE_SERV between "20170701" and "20170930" and USEDRUG = 1 then pid end) work_t2 ,count(case when DATE_SERV between "20161001" and "20170930" and USEDRUG = 1 then pid end) sum_work ,count( pid) target from nhso_tmpqof0052_diag60 LEFT JOIN chospital_amp on chospital_amp.hoscode = nhso_tmpqof0052_diag60.HOSPCODE group by hospcode