select
village.villcode,
villname
,sum(case when getagemonth(person.birth,'2014-07-01') between 6 and 48 and person.typelive in ('1','3') THEN 1 ELSE '' END) AS '6 ด.-3.11 ด'

from person inner join house on person.pcucodeperson = house.pcucode and person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND 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 right(village.villcode,2)<>'00'
group by villname
ORDER BY villcode;