select h.hoscode hospcode,cast(h.hosname as char(200)) hospname,count(*) target,sum(result="Y") result,sum(result="Y")/count(*)*100 percent from chospital_amp h left join ( select p.hospcode,p.cid,p.hn,p.pid,concat(p.name," ",p.lname) ptname ,p.birth,timestampdiff(year,p.birth,"2018-10-01") age ,group_concat(distinct if(a.diagcode regexp "Z123",a.diagcode,null)) dx ,group_concat(distinct if(a.diagcode regexp "Z123","Y",null)) result from person p left join diagnosis_opd a on a.pid=p.pid and a.hospcode=p.hospcode and a.date_serv between "2018-10-01" and "2019-09-30" where timestampdiff(year,p.birth,"2018-10-01") between 30 and 60 and p.typearea in (1,3) and p.sex=2 and p.discharge = 9 group by p.hospcode,p.pid ) a on a.hospcode=h.hoscode group by a.hospcode union select "ภาพรวมทั้งคัพ" as hoscode,"" as hosname,count(*) target,sum(result="Y") result,sum(result="Y")/count(*)*100 percent from chospital_amp h left join ( select p.hospcode,p.cid,p.hn,p.pid,concat(p.name," ",p.lname) ptname ,p.birth,timestampdiff(year,p.birth,"2018-10-01") age ,group_concat(distinct if(a.diagcode regexp "Z123",a.diagcode,null)) dx ,group_concat(distinct if(a.diagcode regexp "Z123","Y",null)) result from person p left join diagnosis_opd a on a.pid=p.pid and a.hospcode=p.hospcode and a.date_serv between "2018-10-01" and "2019-09-30" where timestampdiff(year,p.birth,"2018-10-01") between 30 and 60 and p.typearea in (1,3) and p.sex=2 and p.discharge = 9 group by p.hospcode,p.pid ) a on a.hospcode=h.hoscode