SELECT
village.villno,
village.villname,
count(DISTINCT person.pid) AS 'หญิงตั้งครรภ์ทั้งหมด',
SUM(CASE WHEN visitanc.pregage <= '12' then 1 ELSE '0' END) as 'ฝากก่อน 12 wks',
SUM(CASE WHEN visitanc.pregage <= '12' then 1 ELSE '0' END) * 100/count(DISTINCT person.pid) AS 'ร้อยละ'
FROM
person
INNER JOIN visit ON person.pcucodeperson = visit.pcucode AND person.pid = visit.pid
INNER JOIN visitanc ON visit.pcucode = visitanc.pcucode AND visit.visitno = visitanc.visitno
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
WHERE
visit.visitdate BETWEEN '20140401' AND '20150331'
AND person.nation = '99'
GROUP BY
village.villno
UNION
SELECT
' ',
'รวม',
count(DISTINCT person.pid) AS 'หญิงตั้งครรภ์ทั้งหมด',
SUM(CASE WHEN visitanc.pregage <= '12' then 1 ELSE '0' END) as 'ฝากก่อน 12 wks',
SUM(CASE WHEN visitanc.pregage <= '12' then 1 ELSE '0' END) * 100/count(DISTINCT person.pid) AS 'ร้อยละ'
FROM
person
INNER JOIN visit ON person.pcucodeperson = visit.pcucode AND person.pid = visit.pid
INNER JOIN visitanc ON visit.pcucode = visitanc.pcucode AND visit.visitno = visitanc.visitno
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
WHERE
visit.visitdate BETWEEN '20140401' AND '20150331'
AND person.nation = '99'