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 'รวมเป็น' 
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') < 5 then 1 else '' end) as '0-5ปี'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') between 5 and 14 then 1 else '' end) as '5-14ปี'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') between 15 and 21 then 1 else '' end) as '15-21ปี'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') between 15 and 59 then 1 else '' end) as '15-59ปี'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') >= 60 then 1 else '' end) as '60ปี'
,sum(case when concat(person.pcucodeperson,person.pid)in(select concat(personunable.pcucodeperson,personunable.pid )from personunable ) then 1 else '' end) as 'พิการ'
from person inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
inner join village on house.villcode= village.villcode
where concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=curdate() or deaddate is not null)
and SUBSTRING(house.villcode,7,2)<>'00' and person.typelive in ('1','3')
group by villno,villname;