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 ,group_concat(distinct if(weight>0,"Y",null)) weight ,group_concat(distinct if(height>0,"Y",null)) height ,group_concat(distinct if(sbp_1>0,"Y",null)) sbp ,group_concat(distinct if(dbp_1>0,"Y",null)) dbp ,group_concat(distinct if(bslevel>0,"Y",null)) bslevel ,if(group_concat(distinct if(weight>0,"Y",null))="Y" and group_concat(distinct if(height>0,"Y",null))="Y" and group_concat(distinct if(sbp_1>0,"Y",null))="Y" and group_concat(distinct if(dbp_1>0,"Y",null))="Y" and group_concat(distinct if(bslevel>0,"Y",null))="Y","Y",null) result from person p left join ncdscreen a on a.pid=p.pid and a.hospcode=p.hospcode and a.date_serv between "2018-10-01" and "2019-09-30" left join chronic c on c.pid=p.pid and c.hospcode=p.hospcode and c.chronic between "I10" and "I1599" and c.date_diag<"2018-10-01" where timestampdiff(year,p.birth,"2018-10-01")>=35 and p.typearea in (1,3) and p.discharge = 9 and c.pid is null 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 ,group_concat(distinct if(weight>0,"Y",null)) weight ,group_concat(distinct if(height>0,"Y",null)) height ,group_concat(distinct if(sbp_1>0,"Y",null)) sbp ,group_concat(distinct if(dbp_1>0,"Y",null)) dbp ,group_concat(distinct if(bslevel>0,"Y",null)) bslevel ,if(group_concat(distinct if(weight>0,"Y",null))="Y" and group_concat(distinct if(height>0,"Y",null))="Y" and group_concat(distinct if(sbp_1>0,"Y",null))="Y" and group_concat(distinct if(dbp_1>0,"Y",null))="Y" and group_concat(distinct if(bslevel>0,"Y",null))="Y","Y",null) result from person p left join ncdscreen a on a.pid=p.pid and a.hospcode=p.hospcode and a.date_serv between "2018-10-01" and "2019-06-30" left join chronic c on c.pid=p.pid and c.hospcode=p.hospcode and c.chronic between "I10" and "I1599" and c.date_diag<"2018-10-01" where timestampdiff(year,p.birth,"2018-10-01")>=35 and p.typearea in (1,3) and p.discharge = 9 and c.pid is null group by p.hospcode,p.pid ) a on a.hospcode=h.hoscode