SELECT
village.villno,
village.villname,
COUNT(person.pid) AS 'ทั้งหมด',
SUM(CASE WHEN women.fptype > 0 THEN 1 ELSE NULL END) AS 'วางแผน',
SUM(CASE WHEN women.fptype = '1' THEN 1 ELSE NULL END) AS 'ยาเม็ด',
SUM(CASE WHEN women.fptype = '2' THEN 1 ELSE NULL END) AS 'ยาฉีด',
SUM(CASE WHEN women.fptype = '3' THEN 1 ELSE NULL END) AS 'ยาฝัง',
SUM(CASE WHEN women.fptype = '4' THEN 1 ELSE NULL END) AS 'ห่วง',
SUM(CASE WHEN women.fptype = '5' THEN 1 ELSE NULL END) AS 'ถุงยาง',
SUM(CASE WHEN women.fptype = '6' THEN 1 ELSE NULL END) AS 'หมันชาย',
SUM(CASE WHEN women.fptype = '7' THEN 1 ELSE NULL END) AS 'หมันหญิง',
SUM(CASE WHEN women.fptype = '9' THEN 1 ELSE NULL END) AS 'อื่น'
FROM
village
INNER JOIN house ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN person ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN women ON person.pcucodeperson = women.pcucodeperson AND person.pid = women.pid
WHERE
right(village.villcode,2) <> '00'
and person.pid not in (select persondeath.pid from persondeath)
and person.typelive IN ('1', '3')
and person.dischargetype = '9'
AND getAgeYearNum(birth,CURDATE()) BETWEEN '15' AND '49'
GROUP BY
village.villno
UNION
SELECT
' ',
'รวม',
COUNT(person.pid) AS 'ทั้งหมด',
SUM(CASE WHEN women.fptype > 0 THEN 1 ELSE NULL END) AS 'วางแผน',
SUM(CASE WHEN women.fptype = '1' THEN 1 ELSE NULL END) AS 'ยาเม็ด',
SUM(CASE WHEN women.fptype = '2' THEN 1 ELSE NULL END) AS 'ยาฉีด',
SUM(CASE WHEN women.fptype = '3' THEN 1 ELSE NULL END) AS 'ยาฝัง',
SUM(CASE WHEN women.fptype = '4' THEN 1 ELSE NULL END) AS 'ห่วง',
SUM(CASE WHEN women.fptype = '5' THEN 1 ELSE NULL END) AS 'ถุงยาง',
SUM(CASE WHEN women.fptype = '6' THEN 1 ELSE NULL END) AS 'หมันชาย',
SUM(CASE WHEN women.fptype = '7' THEN 1 ELSE NULL END) AS 'หมันหญิง',
SUM(CASE WHEN women.fptype = '9' THEN 1 ELSE NULL END) AS 'อื่น'
FROM
village
INNER JOIN house ON house.pcucode = village.pcucode AND house.villcode = village.villcode
INNER JOIN person ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN women ON person.pcucodeperson = women.pcucodeperson AND person.pid = women.pid
WHERE
right(village.villcode,2) <> '00'
and person.pid not in (select persondeath.pid from persondeath)
and person.typelive IN ('1', '3')
and person.dischargetype = '9'
AND getAgeYearNum(birth,CURDATE()) BETWEEN '15' AND '49';