SELECT COALESCE(village.villname,'รวม') AS villname
,COUNT(person.pid) AS Total
,Sum(IF(person.sex = 1, 1, 0)) AS 'Male'
,Sum(IF(person.sex = 2, 1, 0)) AS 'Female'
FROM person
LEFT JOIN persondeath ON person.pcucodeperson = persondeath.pcucodeperson AND person.pid = persondeath.pid
LEFT JOIN house ON house.pcucode = person.pcucodeperson AND house.hcode = person.hcode
LEFT JOIN village ON village.pcucode = house.pcucode AND village.villcode = house.villcode
WHERE persondeath.pid IS NULL
GROUP BY village.villname
WITH ROLLUP;