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 ,l.bdate ,group_concat(if(a.ga<=14 and a.ancno=1,a.ga,null) order by a.date_serv) ancno1 ,group_concat(if(a.ga between 15 and 20 and a.ancno=2,a.ga,null) order by a.date_serv) ancno2 ,group_concat(if(a.ga between 21 and 28 and a.ancno=3,a.ga,null) order by a.date_serv) ancno3 ,group_concat(if(a.ga between 29 and 34 and a.ancno=4,a.ga,null) order by a.date_serv) ancno4 ,group_concat(if(a.ga between 35 and 42 and a.ancno=5,a.ga,null) order by a.date_serv) ancno5 ,if(group_concat(distinct if(a.ga<=14 and a.ancno=1,"Y",null))="Y" and group_concat(distinct if(a.ga between 15 and 20 and a.ancno=2,"Y",null))="Y" and group_concat(distinct if(a.ga between 21 and 28 and a.ancno=3,"Y",null))="Y" and group_concat(distinct if(a.ga between 29 and 34 and a.ancno=4,"Y",null))="Y" and group_concat(distinct if(a.ga between 35 and 42 and a.ancno=5,"Y",null))="Y","Y",null) result from labor l join person p on p.pid=l.pid and l.hospcode=p.hospcode left join anc a on a.pid=l.pid and a.hospcode=l.hospcode where l.bdate between "2018-10-01" and "2019-09-30" and p.typearea in (1,3) and p.discharge = 9 and l.btype in (1,2,3,4,5) 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 ,l.bdate ,group_concat(if(a.ga<=14 and a.ancno=1,a.ga,null) order by a.date_serv) ancno1 ,group_concat(if(a.ga between 15 and 20 and a.ancno=2,a.ga,null) order by a.date_serv) ancno2 ,group_concat(if(a.ga between 21 and 28 and a.ancno=3,a.ga,null) order by a.date_serv) ancno3 ,group_concat(if(a.ga between 29 and 34 and a.ancno=4,a.ga,null) order by a.date_serv) ancno4 ,group_concat(if(a.ga between 35 and 42 and a.ancno=5,a.ga,null) order by a.date_serv) ancno5 ,if(group_concat(distinct if(a.ga<=14 and a.ancno=1,"Y",null))="Y" and group_concat(distinct if(a.ga between 15 and 20 and a.ancno=2,"Y",null))="Y" and group_concat(distinct if(a.ga between 21 and 28 and a.ancno=3,"Y",null))="Y" and group_concat(distinct if(a.ga between 29 and 34 and a.ancno=4,"Y",null))="Y" and group_concat(distinct if(a.ga between 35 and 42 and a.ancno=5,"Y",null))="Y","Y",null) result from labor l join person p on p.pid=l.pid and l.hospcode=p.hospcode left join anc a on a.pid=l.pid and a.hospcode=l.hospcode where l.bdate between "2018-10-01" and "2019-09-30" and p.typearea in (1,3) and p.discharge = 9 and l.btype in (1,2,3,4,5) group by p.hospcode,p.pid ) a on a.hospcode=h.hoscode