ดึงจำนวนผู้สูงอายุ ผู้พิการ ผู้ป่วย NCD แยกตามสภาพปัญหา รายหมู่


select village.villname,
sum(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable ) then '1'else '' end) as 'พิการ',
sum(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '1' then '1' else '' end) as 'พิการกลุ่ม1',
sum(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '2' then '1' else '' end) as 'พิการกลุ่ม2',
sum(case when concat(person.pcucodeperson,person.pid)in (select concat(personunable.pcucodeperson,personunable.pid )
from personunable )and person.candobedhomesocial = '3' then '1' else '' end) as 'พิการกลุ่ม3',
sum(case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where  personchronic.typedischart='03' ) then '1' else '' end) as 'NCD',
sum(case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where  personchronic.typedischart='03' )and person.candobedhomesocial = '1' then '1' else '' end) as 'NCDกลุ่ม1',
sum(case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where  personchronic.typedischart='03' )and person.candobedhomesocial = '2' then '1' else '' end) as 'NCDกลุ่ม2',
sum(case when concat(person.pcucodeperson,person.pid) in (select concat(personchronic.pcucodeperson,personchronic.pid )
from personchronic where  personchronic.typedischart='03' )and person.candobedhomesocial = '3' then '1' else '' end) as 'NCDกลุ่ม3',
sum(case when  getAgeYearNum(person.birth,'2014-10-01')>= 60 then '1'else '' end) as 'Old',
sum(case when getAgeYearNum(person.birth,'2014-10-01')>= 60 and person.candobedhomesocial = '1' then '1' else '' end) as 'Oldกลุ่ม1',
sum(case when getAgeYearNum(person.birth,'2014-10-01')>= 60 and person.candobedhomesocial = '2' then '1' else '' end) as 'Oldกลุ่ม2',
sum(case when getAgeYearNum(person.birth,'2014-10-01')>= 60 and person.candobedhomesocial = '3' then '1' else '' end) as 'Oldกลุ่ม3'
from person inner join ctitle on person.prename = ctitle.titlecode
inner join house on person.pcucodeperson = house.pcucode  and person.hcode = house.hcode
inner join village on house.pcucode = village.pcucode and village.villcode = house.villcode
left join user on house.usernamedoc = user.username
where person.typelive IN ('1','3')and person.pid not in (select persondeath.pid from persondeath)and substring(house.villcode,7,2)<>'00'
group by village.villcode