select h.hoscode as hospcode ,h.hosname as hospname,type1,type2,type3,type4,type5,nottype,total from chospital_amp h left join (select person.hospcode ,count(*) as total ,sum(if(person.typearea='1',1,0)) as type1 ,sum(if(person.typearea='2',1,0)) as type2 ,sum(if(person.typearea='3',1,0)) as type3 ,sum(if(person.typearea='4',1,0)) as type4 ,sum(if(person.typearea='5',1,0)) as type5 ,sum(if(person.typearea not in ('1','2','3','4','5'),1,0)) as nottype from person #where person.discharge = '9' and person.nation ='099' group by person.hospcode order by hospcode) as pa on h.hoscode = pa.hospcode