SELECT CASE WHEN person.sex = '1' THEN 'ชาย'WHEN person.sex = '2' THEN 'หญิง'ELSE'ไม่ระบุ'END AS groupsex,
CASE WHEN age_year between '15' and '34' THEN '15-34 'WHEN age_year between '35' and '59' THEN '35-39'WHEN age_year >='60' THEN '>60'ELSE'ไม่ระบุ'END AS grouppop,
count(ncd_person_ncd_screen.pid) as 'จำนวนคัดกรอง' ,
sum(CASE WHEN screen_q1 = '1' THEN '1' ELSE null END) AS 'ญาติเบาหวาน',
sum(CASE WHEN screen_q2 = '1' THEN '1' ELSE null END) AS 'รอบเอว+BMIเกิน',
sum(CASE WHEN screen_q3 = '1' THEN '1' ELSE null END) AS 'BP>140/90',
sum(CASE WHEN screen_q4 = '1' THEN '1' ELSE null END) AS 'ประวัติน้ำตาลสูง',
sum(CASE WHEN screen_q5 = '1' THEN '1' ELSE null END) AS 'Tri>250',
sum(CASE WHEN screen_q6 = '1' THEN '1' ELSE null END) AS 'เบาหวานขณะตั้งครรภ์',
sum(CASE WHEN blackarmpit = '1' THEN '1' ELSE null END) AS 'รอยพับดำ',
sum(CASE WHEN htfamily = '1' THEN '1' ELSE null END) AS 'ญาติBPสูง',
sum(CASE WHEN ((hbp_s1 <'120' and hbp_d1 <'80')OR (hbp_s2 <'120' and hbp_d2 <'80')) THEN '1' ELSE null END) AS 'HTขาว',
sum(CASE WHEN ((hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89')) THEN '1' ELSE null END) AS 'HTเขียว',
sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DMขาว',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DMเขียว'

FROM
ncd_person_ncd_screen INNER JOIN ncd_person ON ncd_person_ncd_screen.pcucode = ncd_person.pcucode AND ncd_person_ncd_screen.pid = ncd_person.pid
INNER JOIN person ON ncd_person_ncd_screen.pcucode = person.pcucodeperson AND ncd_person_ncd_screen.pid = person.pid
WHERE
ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-09-30'
and SUBSTRING(ncd_person.village,7,2) !='00' and person.typelive in ('1','3') and CONCAT(person.pid,person.pcucodeperson) not in (SELECT CONCAT(persondeath.pid,persondeath.pcucodeperson)
FROM persondeath)
group by grouppop ,groupsex;