select village.villcode AS 'ชื่อหมู่บ้าน',
village.villname AS 'หมู่ที่',
COUNT(DISTINCT house.hcode) as 'หลังคาเรือน' ,
sum(if(sex ='1',1,0)) 'ชาย', 
sum(if(sex ='2',1,0))'หญิง' , 
count(person.pid) as 'รวมเป็น' 
from person
INNER JOIN house ON person.hcode=house.hcode
INNER JOIN village ON village.villcode = house.villcode
LEFT JOIN persondeath ON person.pid=persondeath.pid 
AND person.pcucodeperson=persondeath.pcucodeperson
where persondeath.pid IS NULL
AND person.dischargetype <> 1 
and TIMESTAMPDIFF(year,birth,now())between '0' and '200' 
and village.villcode<> '1' 
AND person.nation= '99' 
AND person.sex IN (1,2)
GROUP BY village.villcode
ORDER BY village.villcode;
#create by rambo