SELECT
village.villname,
COUNT(*) AS เป้าหมายอายุ15ปีขึ้นไป,
COUNT(ncd_person_ncd_screen.pid) AS ยอดการคัดกรอง,
ROUND((COUNT(ncd_person_ncd_screen.pid)/COUNT(*))*100,2) AS คัดกรองร้อยละ,
(COUNT(*)- COUNT(ncd_person_ncd_screen.pid)) AS ยังไม่ได้คัดกรอง,
ROUND((100-((COUNT(ncd_person_ncd_screen.pid)/COUNT(*))*100)),2) AS ไม่ได้คัดกรองร้อยละ
FROM person
INNER JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
INNER JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
LEFT JOIN ncd_person_ncd_screen ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid 
WHERE CONCAT(person.pid,person.pcucodeperson)
NOT IN (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath
WHERE persondeath.deaddate <= CURDATE() or deaddate is not NULL)
AND SUBSTRING(house.villcode,7,2) != '00'
AND YEAR( FROM_DAYS( DATEDIFF( NOW( ) ,person.birth ) ) ) >= 15
GROUP BY village.villname
ORDER BY house.villcode;