SELECT CASE WHEN person.sex = '1' THEN 'ชาย'WHEN person.sex = '2' THEN 'หญิง'ELSE'ไม่ระบุ'END AS groupsex,
CASE 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 hbp_s1 <'120' and hbp_d1 <'80' THEN '1' ELSE null END) AS 'BP120-80',
sum(CASE WHEN ((hbp_s1 between '120' and '139') or (hbp_d1 between '80' and '89')) THEN '1' ELSE null END) AS 'BP120/80-139/89',
sum(CASE WHEN bsl < '100' THEN '1' ELSE null END) AS 'DTX<100',
sum(CASE WHEN bsl between '100' and '125' THEN '1' ELSE null END) AS 'DTX100-125'
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)
and concat(ncd_person_ncd_screen.pid,ncd_person_ncd_screen.pcucode)NOT IN(select concat(personchronic.pid,personchronic.pcucodeperson)from personchronic where personchronic.chroniccode like'E1%' or(personchronic.chroniccode = 'I10'))
group by grouppop ,groupsex