SELECT chospital_amp.hoscode as hoscode, chospital_amp.hosname as hosname, sum(case when MONTH(surveillance.DATE_SERV) ="10" and YEAR(surveillance.DATE_SERV) ="2017" then 1 else 0 end) as Oct2560, sum(case when MONTH(surveillance.DATE_SERV) ="11" and YEAR(surveillance.DATE_SERV) ="2017" then 1 else 0 end) as Nov2560, sum(case when MONTH(surveillance.DATE_SERV) ="12" and YEAR(surveillance.DATE_SERV) ="2017" then 1 else 0 end) as Dec2560, sum(case when MONTH(surveillance.DATE_SERV) ="01" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as Jan2561, sum(case when MONTH(surveillance.DATE_SERV) ="02" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as Feb2561, sum(case when MONTH(surveillance.DATE_SERV) ="03" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as Mar2561, sum(case when MONTH(surveillance.DATE_SERV) ="04" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as Apr2561, sum(case when MONTH(surveillance.DATE_SERV) ="05" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as May2561, sum(case when MONTH(surveillance.DATE_SERV) ="06" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as Jun2561, sum(case when MONTH(surveillance.DATE_SERV) ="07" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as July2561, sum(case when MONTH(surveillance.DATE_SERV) ="08" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as Aug2561, sum(case when MONTH(surveillance.DATE_SERV) ="09" and YEAR(surveillance.DATE_SERV) ="2018" then 1 else 0 end) as Sep2561 FROM chospital_amp Inner Join surveillance ON surveillance.HOSPCODE = chospital_amp.hoscode group by hoscode