 SELECT village.villcode,villname ,count(person.pid) as 'พิการ'
FROM
person INNER JOIN personunable ON person.pcucodeperson = personunable.pcucodeperson
inner join house on person.hcode=house.hcode and person.pcucodeperson=house.pcucode
inner join village on house.villcode = village.villcode and house.pcucode = village.pcucode
AND person.pid = personunable.pid
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 village.villcode;

