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 ,cc.instype_new ,group_concat(distinct if(c.chronic between "I10" and "I1599",c.chronic,null) order by c.chronic) chronic ,group_concat(distinct if(d1.diagcode regexp "I1(0|1|2|3|4|5)|I674","Y",null)) Case1 ,group_concat(distinct if(d1.diagcode regexp "I60|I61|I62" and d2.diagcode regexp "I1(0|1|2|3|4|5)|I674" and d2.diagcode not regexp "^[S-T]" ,"Y",null)) Case2 ,group_concat(distinct if(d1.diagcode regexp "H350" and d2.diagcode regexp "I1(0|1|2|3|4|5)|I674","Y",null)) Case3 ,if(group_concat(distinct if(d1.diagcode regexp "I1(0|1|2|3|4|5)|I674","Y",null))="Y" or group_concat(distinct if(d1.diagcode regexp "I60|I61|I62" and d2.diagcode regexp "I1(0|1|2|3|4|5)|I674" and d2.diagcode not regexp "^[S-T]" ,"Y",null))="Y" or group_concat(distinct if(d1.diagcode regexp "H350" and d2.diagcode regexp "I1(0|1|2|3|4|5)|I674","Y",null))="Y","Y",null) result from person p join card cc on cc.pid=p.pid and cc.hospcode=p.hospcode left join chronic c on c.pid=p.pid and c.hospcode=p.hospcode left join admission a on a.pid=p.pid and a.hospcode=p.hospcode and date(a.datetime_disch) between "2015-04-01" and "2016-03-31" left join diagnosis_ipd d1 on a.an=d1.an and a.hospcode=d1.hospcode and d1.diagtype=1 left join diagnosis_ipd d2 on a.an=d2.an and a.hospcode=d2.hospcode and d2.diagtype<>1 where cc.instype_new=0100 and p.typearea in (1,3) and p.discharge = "9" and c.chronic between "I10" and "I1599" 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 ,cc.instype_new ,group_concat(distinct if(c.chronic between "I10" and "I1599",c.chronic,null) order by c.chronic) chronic ,group_concat(distinct if(d1.diagcode regexp "I1(0|1|2|3|4|5)|I674","Y",null)) Case1 ,group_concat(distinct if(d1.diagcode regexp "I60|I61|I62" and d2.diagcode regexp "I1(0|1|2|3|4|5)|I674" and d2.diagcode not regexp "^[S-T]" ,"Y",null)) Case2 ,group_concat(distinct if(d1.diagcode regexp "H350" and d2.diagcode regexp "I1(0|1|2|3|4|5)|I674","Y",null)) Case3 ,if(group_concat(distinct if(d1.diagcode regexp "I1(0|1|2|3|4|5)|I674","Y",null))="Y" or group_concat(distinct if(d1.diagcode regexp "I60|I61|I62" and d2.diagcode regexp "I1(0|1|2|3|4|5)|I674" and d2.diagcode not regexp "^[S-T]" ,"Y",null))="Y" or group_concat(distinct if(d1.diagcode regexp "H350" and d2.diagcode regexp "I1(0|1|2|3|4|5)|I674","Y",null))="Y","Y",null) result from person p join card cc on cc.pid=p.pid and cc.hospcode=p.hospcode left join chronic c on c.pid=p.pid and c.hospcode=p.hospcode left join admission a on a.pid=p.pid and a.hospcode=p.hospcode and date(a.datetime_disch) between "2015-04-01" and "2016-03-31" left join diagnosis_ipd d1 on a.an=d1.an and a.hospcode=d1.hospcode and d1.diagtype=1 left join diagnosis_ipd d2 on a.an=d2.an and a.hospcode=d2.hospcode and d2.diagtype<>1 where cc.instype_new=0100 and p.typearea in (1,3) and p.discharge = "9" and c.chronic between "I10" and "I1599" group by p.hospcode,p.pid ) a on a.hospcode=h.hoscode