select
village.villcode,villname
,sum(case when ciga = 0 then 1 else 0 end) as 'ไม่สูบ'
,sum(case when ciga = 1 then 1 else 0 end) as 'สูบนานๆครั้ง'
,sum(case when ciga = 2 then 1 else 0 end) as 'สูบประจำ'

,sum(case when wisky = 0 then 1 else 0 end) as 'ไม่ดื่ม'
,sum(case when wisky =1 then 1 else 0 end) as 'ดื่มนานๆครั้ง'
,sum(case when wisky = 2 then 1 else 0 end) as '1-2ครั้งต่อเดือน'
,sum(case when wisky = 3 then 1 else 0 end) as '1-2ครั้งต่อสัปดาห์'
,sum(case when wisky = 4 then 1 else 0 end) as '3-4ครั้งต่อสัปดาห์'
,sum(case when wisky = 5 then 1 else 0 end) as 'ดื่มทุกวัน'

,sum(case when exercise = 0 then 1 else 0 end) as 'ไม่ออกกำลังกาย'
,sum(case when exercise = 1 then 1 else 0 end) as 'ออก วัน'
,sum(case when exercise = 2 then 1 else 0 end) as 'ออก 3-5วัน'
,sum(case when exercise = 3 then 1 else 0 end) as 'ออก>5วัน'
from person left join personbehavior on person.pid = personbehavior.pid and person.pcucodeperson = personbehavior.pcucodeperson
left join house on person.hcode = house.hcode and person.pcucodeperson = house.pcucode
left join village on house.villcode = village.villcode and house.pcucode = village.pcucode
where substring(house.villcode,7,2) !='00'
and GetAgeYearNum(person.birth,curdate()) < 15
and concat(person.pid,person.pcucodeperson) not in (select concat(persondeath.pid,persondeath.pcucodeperson) from persondeath where persondeath.pcucodeperson=person.pcucodeperson and (persondeath.deaddate is null or persondeath.deaddate<=curdate()))
group by village.villcode;