select house.villcode as villcode
,count(person.pid) as 'total'
,sum(case when person.typelive='1' then 1 else 0 end) as '1'
,sum(case when person.typelive='2' then 1 else 0 end) as '2'
,sum(case when person.typelive='3' then 1 else 0 end) as '3'
,sum(case when person.typelive='4' then 1 else 0 end) as '4'
,sum(case when person.typelive in ('1','3') then 1 else 0 end) as '1+3'
,sum(case when GetAgeYearNum(person.birth,'2014-10-01') >= 40 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '40ปี'
from person inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
where concat(person.pid,person.pcucodeperson)not in (select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath where deaddate<=curdate() or deaddate is not null)
group by villcode;