select village.villno as หมู่
,village.villname
,count(distinct person.pid) as เป้า 
,count(distinct sc.pid) as ผลงาน
,concat(round(count(distinct sc.pid)*100/count(distinct person.pid),2),' %') as เปอรเซ็น 
,count(distinct case when person.birth BETWEEN '1978-10-1' AND '1998-9-30' then person.pid else null end ) as "เป้า15-34" 
,count(distinct case when person.birth BETWEEN '1978-10-1' AND '1998-9-30' and sc.pid is not null then person.pid else null end) as ผลงาน 
,concat(round(count(distinct case when person.birth BETWEEN '1978-10-1' AND '1998-9-30' 
and sc.pid is not null then person.pid else null end)*100 / count(distinct case when person.birth BETWEEN '1978-10-1' AND '1998-9-30' then person.pid else null end),2),' %') as "%15-34"
,count(distinct case when person.birth BETWEEN '1953-10-1' AND '1978-9-30' then person.pid else null end) as "เป้า35-59" ,count(distinct case when person.birth BETWEEN '1953-10-1' AND '1978-9-30' and sc.pid is not null then person.pid else null end) as ผลงาน 
,concat(round(count(distinct case when person.birth BETWEEN '1953-10-1' AND '1978-9-30' 
and sc.pid is not null then person.pid else null end)*100 / count(distinct case when person.birth BETWEEN '1953-10-1' AND '1978-9-30' then person.pid else null end),2),' %') as "%35-59" ,count(case when person.birth <'1953-10-1' then 1 else null end) as "เป้า>60" ,count(distinct case when person.birth <'1953-10-1' and sc.pid is not null then person.pid else null end) as ผลงาน ,concat(round(count(distinct case when person.birth <'1953-10-1' 
and sc.pid is not null then person.pid else null end)*100 / count(distinct case when person.birth <'1953-10-1' then person.pid else null end),2),' %') as "%>60" 
from person 
left join ncd_person_ncd_screen sc on person.pid = sc.pid and person.pcucodeperson = sc.pcucode 
and sc.screen_date between '20131001' and '20140930' 
inner join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode 
inner join village on house.villcode= village.villcode and villno <> '00' 
where person.birth <='1998-10-1' and (person.typelive in ('0','1','3')) and person.pid not in (select pid from personchronic WHERE (personchronic.chroniccode BETWEEN 'e10' and 'e14') OR (personchronic.chroniccode BETWEEN 'i10' and 'i15')) 
group by villname order by villno;