SELECT
village.villname,
village.villno AS mu,
count(person.pid) AS 'เป้าหมาย',
COUNT(CASE WHEN visitlabcancer.typecancer = '1' AND visitlabcancer.datecheck BETWEEN '20131001' AND '20140930' THEN '1' ELSE NULL END) as 'ผลงาน',
COUNT(CASE WHEN visitlabcancer.typecancer = '1' AND visitlabcancer.datecheck BETWEEN '20131001' AND '20140930' THEN '1' ELSE NULL END) *100 / count(person.pid) as 'ร้อยละ',
COUNT(CASE WHEN visitlabcancer.datecheck BETWEEN '20131001' AND '20131031' THEN '1' ELSE NULL END) AS 'ตุลา56',
COUNT(CASE WHEN visitlabcancer.datecheck BETWEEN '20131101' AND '20131130' THEN '1' ELSE NULL END) AS 'พย56',
COUNT(CASE WHEN visitlabcancer.datecheck BETWEEN '20131201' AND '20131231' THEN '1' ELSE NULL END) AS 'ธค56',
COUNT(CASE WHEN visitlabcancer.datecheck BETWEEN '20140101' AND '20140131' THEN '1' ELSE NULL END) AS 'มค57'
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
left JOIN visitlabcancer ON person.pcucodeperson = visitlabcancer.pcucodeperson AND person.pid = visitlabcancer.pid
WHERE
person.sex = 2
AND getAgeYearNum (birth,CURDATE()) BETWEEN '30' AND '70'
GROUP BY
mu;