SELECT
person.religion,creligion.religionname
,SUM(case right(house.villcode,2) when '01' then 1 else 0 end ) as mu01
,SUM(case right(house.villcode,2) when '02' then 1 else 0 end ) as mu02
,SUM(case right(house.villcode,2) when '03' then 1 else 0 end ) as mu03
,SUM(case right(house.villcode,2) when '04' then 1 else 0 end ) as mu04
,SUM(case right(house.villcode,2) when '05' then 1 else 0 end ) as mu05
,SUM(case right(house.villcode,2) when '06' then 1 else 0 end ) as mu06
,SUM(case right(house.villcode,2) when '07' then 1 else 0 end ) as mu07
,SUM(case right(house.villcode,2) when '08' then 1 else 0 end ) as mu08
,SUM(case right(house.villcode,2) when '09' then 1 else 0 end ) as mu09
,SUM(case right(house.villcode,2) when '10' then 1 else 0 end ) as mu10
,SUM(case right(house.villcode,2) when '11' then 1 else 0 end ) as mu11
,SUM(case right(house.villcode,2) when '12' then 1 else 0 end ) as mu12
,SUM(case right(house.villcode,2) when '13' then 1 else 0 end ) as mu13
,SUM(case right(house.villcode,2) when '14' then 1 else 0 end ) as mu14
,SUM(case right(house.villcode,2) when '15' then 1 else 0 end ) as mu15
,SUM(case right(house.villcode,2) when '16' then 1 else 0 end ) as mu16


FROM person
INNER JOIN house ON person.pcucodeperson=house.pcucodeperson AND person.hcode=house.hcode
INNER JOIN creligion on person.religion=creligion.religioncode


WHERE right(house.villcode,2)!='00'
and CONCAT(person.pid,person.pcucodeperson) not IN (select CONCAT(persondeath.pid,persondeath.pcucodeperson)from persondeath
where persondeath.deaddate <= 'year(CURDATE()-07-01)'or persondeath.dateupdate is NULL)
AND CONCAT(person.pid,person.pcucodeperson) not in (select CONCAT(personstudent.pid,personstudent.pcucodeperson)from personstudent)

and person.typelive in('1','3')


GROUP BY creligion.religioncode;