SELECT right(house.villcode,2)as mu
,COUNT(DISTINCT if(getAgeYearNum(person.birth,CURDATE())BETWEEN 10 and 14,person.pid,null))as '10-14'
, COUNT(DISTINCT CASE when getAgeYearNum(person.birth,CURDATE())BETWEEN 10 and 14 and ncd.smoke in ('2','3','4') then ncd.pid ELSE null end) as 's10-14'
, COUNT(DISTINCT CASE when getAgeYearNum(person.birth,CURDATE())BETWEEN 10 and 14 and ncd.alcohol in ('2','3','4') then ncd.pid ELSE null end) as 'a10-14'
,COUNT(DISTINCT if(getAgeYearNum(person.birth,CURDATE())BETWEEN 15 and 19,person.pid,null))as '15-19'
, COUNT(DISTINCT CASE when getAgeYearNum(person.birth,CURDATE())BETWEEN 15 and 19 and ncd.smoke in ('2','3','4') then ncd.pid ELSE null end) as 's15-19'
, COUNT(DISTINCT CASE when getAgeYearNum(person.birth,CURDATE())BETWEEN 15 and 19 and ncd.alcohol in ('2','3','4') then ncd.pid ELSE null end) as 'a15-15'
,COUNT(DISTINCT if(getAgeYearNum(person.birth,CURDATE())>=20,person.pid,null))as '>20'
, COUNT(DISTINCT CASE when getAgeYearNum(person.birth,CURDATE())>=20 and ncd.smoke in ('2','3','4') then ncd.pid ELSE null end) as 's>20'
, COUNT(DISTINCT CASE when getAgeYearNum(person.birth,CURDATE())>=20 and ncd.alcohol in ('2','3','4') then ncd.pid ELSE null end) as 'a>20'
,COUNT(DISTINCT if(getAgeYearNum(person.birth,CURDATE())>=10,person.pid,null))as 'total'
, COUNT(DISTINCT CASE when getAgeYearNum(person.birth,CURDATE())>=10 and ncd.smoke in ('2','3','4') then ncd.pid ELSE null end) as 's_total'
, COUNT(DISTINCT CASE when getAgeYearNum(person.birth,CURDATE())>=10 and ncd.alcohol in ('2','3','4') then ncd.pid ELSE null end) as 'a_total'
from
person
INNER JOIN house on person.hcode=house.hcode and person.pcucodeperson=house.pcucode
LEFT JOIN ncd_person_ncd_screen as ncd on person.pid = ncd.pid and person.pcucodeperson=ncd.pcucode
WHERE person.pid not in (SELECT persondeath.pid from persondeath) and person.typelive in ('1','3','0')
GROUP BY right(house.villcode,2)
WITH ROLLUP;