SELECT
village.villno AS 'หมู่ที่',
village.villname AS 'หมู่บ้าน',
COUNT(person.pid) AS 'เป้าหมาย',
SUM(CASE WHEN visitanc.pregage <= '12' THEN '1' ELSE '0' END) AS 'ผลงาน',
COUNT(person.pid) * 100 / SUM(CASE WHEN visitanc.pregage <= '12' THEN '1' ELSE '0' END) AS 'ร้อยละ'
FROM
person
INNER JOIN house ON person.pcucodeperson = house.pcucodeperson AND person.hcode = house.hcode
INNER JOIN village ON house.villcode = village.villcode
INNER JOIN visitanc ON person.pcucodeperson = visitanc.pcucodeperson AND person.pid = visitanc.pid
WHERE
visitanc.ancno = '1'
AND person.dischargetype = 9
AND person.typelive IN (1,3)
AND visitanc.datecheck BETWEEN '2013-04-01' AND '2014-03-31'
GROUP BY
village.villname
ORDER BY
villno;