SELECT
village.villno,
village.villname,
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 person.sex IN (1,2) then 1 else 0 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 personchronic ON personchronic.pcucodeperson = person.pcucodeperson AND personchronic.pid = person.pid
INNER JOIN persondeath ON persondeath.pcucodeperson = personchronic.pcucodeperson AND persondeath.pid = personchronic.pid
WHERE
persondeath.deaddate BETWEEN 20141001 AND 20150930
GROUP BY
village.villno;