SELECT
village.villname
,SUBSTR(house.villcode,7,2) AS 'หมู่ที่'
,COUNT(*) AS 'รวมคนทั้งหมด'
,SUM(CASE WHEN person.sex='1' THEN '1' ELSE '0' END) AS 'ชาย'
,SUM(CASE WHEN person.sex='2' THEN '1' ELSE '0' END) AS 'หญิง'
,SUM(CASE WHEN visit.timeservice='1' THEN '1' ELSE '0' END) AS 'ในเวลา'
,SUM(CASE WHEN visit.timeservice='2' THEN '1' ELSE '0' END) AS 'นอกเวลา'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='10' THEN '1' ELSE '0' END) AS 'ตุลาคม'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='11' THEN '1' ELSE '0' END) AS 'พฤศจิกายน'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='12' THEN '1' ELSE '0' END) AS 'ธันวาคม'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='01' THEN '1' ELSE '0' END) AS 'มกราคม'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='02' THEN '1' ELSE '0' END) AS 'กุมภาพันธ์'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='03' THEN '1' ELSE '0' END) AS 'มีนาคม'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='04' THEN '1' ELSE '0' END) AS 'เมษายน'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='05' THEN '1' ELSE '0' END) AS 'พฤษภาคม'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='06' THEN '1' ELSE '0' END) AS 'มิถุนายน'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='07' THEN '1' ELSE '0' END) AS 'กรกฎาคม'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='08' THEN '1' ELSE '0' END) AS 'สิงหาคม'
,SUM(CASE WHEN DATE_FORMAT(visit.visitdate,'%m')='09' 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.pcucodeperson=village.pcucode AND house.villcode=village.villcode
INNER JOIN visit ON visit.pcucodeperson=person.pcucodeperson AND visit.pid=person.pid
WHERE visitdate BETWEEN '2014-10-01' AND '2015-09-30'
GROUP BY village.villname
ORDER BY house.villcode;