SELECT
RIGHT(village.villcode,2) as moo,
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) >= 60 THEN person.pid ELSE NULL END) AS '60 ปีขึ้นไป',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) >= 60 THEN
CASE WHEN person.candobedhomesocial IS NOT NULL THEN person.pid ELSE NULL END ELSE NULL END) AS 'สำรวจ ADL',
FORMAT(COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) >= 60 THEN
CASE WHEN person.candobedhomesocial IS NOT NULL THEN person.pid ELSE NULL END ELSE NULL END)*100/
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) >= 60 THEN person.pid ELSE NULL END),2) as 'ร้อยละ',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) BETWEEN 60 AND 69 THEN
CASE WHEN person.candobedhomesocial = '1' THEN person.pid ELSE NULL END ELSE NULL END) as '6069_grp1',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) BETWEEN 70 AND 79 THEN
CASE WHEN person.candobedhomesocial = '1' THEN person.pid ELSE NULL END ELSE NULL END) as '7079_grp1',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) >= 80 THEN
CASE WHEN person.candobedhomesocial = '1' THEN person.pid ELSE NULL END ELSE NULL END) as '80up_grp1',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) BETWEEN 60 AND 69 THEN
CASE WHEN person.candobedhomesocial = '2' THEN person.pid ELSE NULL END ELSE NULL END) as '6069_grp2',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) BETWEEN 70 AND 79 THEN
CASE WHEN person.candobedhomesocial = '2' THEN person.pid ELSE NULL END ELSE NULL END) as '7079_grp2',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) >= 80 THEN
CASE WHEN person.candobedhomesocial = '2' THEN person.pid ELSE NULL END ELSE NULL END) as '80up_grp2',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) BETWEEN 60 AND 69 THEN
CASE WHEN person.candobedhomesocial = '3' THEN person.pid ELSE NULL END ELSE NULL END) as '6069_grp3',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) BETWEEN 70 AND 79 THEN
CASE WHEN person.candobedhomesocial = '3' THEN person.pid ELSE NULL END ELSE NULL END) as '7079_grp3',
COUNT(DISTINCT CASE WHEN getAgeYearNum(person.birth,CURRENT_DATE()) >= 80 THEN
CASE WHEN person.candobedhomesocial = '3' THEN person.pid ELSE NULL END ELSE NULL END) as '80up_grp3'
FROM
person
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
INNER JOIN village ON house.pcucode = village.pcucode AND house.villcode = village.villcode
WHERE person.typelive IN ('0','1','3') AND dischargetype <> 1
AND RIGHT(village.villcode,2)<>'00'
AND concat(person.pid,person.pcucodeperson)
NOT IN (SELECT concat(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath WHERE deaddate<=curdate() OR deaddate IS NOT NULL)
GROUP BY RIGHT(village.villcode,2)
ORDER BY RIGHT(village.villcode,2);