SELECT CASE WHEN age_year between '15' and '34' THEN 'อายุ15_34' WHEN age_year between '35' and '59' THEN 'อายุ35_59' WHEN age_year >= '60' THEN 'อายุ>60'ELSE null END AS 'Grage',
case when ncd_person.sex = '1' then 'ชาย'when ncd_person.sex = '2' then 'หญิง'else'ไม่ระบุ'end as grsex,
sum(CASE WHEN age_year >= '15' THEN 1 ELSE 0 END) AS 'จำนวนคัดกรอง',
sum(CASE WHEN age_year between '15' and '34' and chronic_flag='0' and (screen_q1 + screen_q2 + screen_q3 + (blackarmpit='1') )>='3' THEN 1 ELSE 0 END) AS 'verbal<15ปี',
sum(CASE WHEN age_year >= '35' and chronic_flag='0' and (screen_q1 + screen_q2 + screen_q3 + screen_q4 + screen_q5 + screen_q6 )>='1' THEN 1 ELSE 0 END) AS 'verbal>35ปี',
sum(CASE WHEN chronic_flag='0' and bsl> '0' THEN 1 ELSE 0 END) AS 'ได้รับการเจาะDTX',
sum(CASE WHEN chronic_flag='0' and bsl < '100' THEN 1 ELSE 0 END) AS 'bsl<100',
sum(CASE WHEN chronic_flag='0' and bsl between '100' and '125' THEN 1 ELSE 0 END) AS 'bsl100-125',
sum(CASE WHEN chronic_flag='0' and bsl >= '126' THEN 1 ELSE 0 END) AS 'bsl>126',
sum(CASE WHEN chronic_flag='0' and ((hbp_s2 between '120' and '139') or (hbp_d2 between '80' and '89')) THEN 1 ELSE 0 END) AS 'BP120/80-139/89',
sum(CASE WHEN chronic_flag='0' and (hbp_s2 >= '140' and hbp_d2 >= '90') THEN 1 ELSE 0 END) AS 'BP>140/90',
sum(CASE WHEN chronic_flag='0' and (hbp_s1 < '120' and hbp_d1 < '80') THEN 1 ELSE 0 END) AS 'BP<120/80',
sum(CASE WHEN ncd_person.sex = '1' and chronic_flag='0' and ncd_person_ncd_screen.waist >= '90' THEN 1 ELSE 0 END) AS 'รอบเอวเกินชาย',
sum(CASE WHEN ncd_person.sex = '2' and chronic_flag='0' and ncd_person_ncd_screen.waist >= '80' THEN 1 ELSE 0 END) AS 'รอบเอวเกินหญิง',
sum(CASE WHEN chronic_flag='0' and ncd_person_ncd_screen.bmi >= '25' THEN 1 ELSE 0 END) AS 'BMI>25'
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 ncd_person_ncd ON ncd_person_ncd_screen.pcucode = ncd_person_ncd.pcucode AND ncd_person_ncd_screen.pid = ncd_person_ncd.pid

WHERE ncd_person_ncd_screen.screen_date between '2014-10-01' and '2015-09-30'
and SUBSTRING(ncd_person.village,7,2)<> '00'
group by Grage,grsex;