select h.hoscode as hospcode ,h.hosname as hospname,type1,type2,sum_type1_2 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 in ("1","3"),1,0)) as sum_type1_3 ,sum(if(person.typearea in ("1","2"),1,0)) as sum_type1_2 from person where person.discharge = "9" and person.nation ="099" group by person.hospcode order by hospcode) as pa on h.hoscode = pa.hospcode