## HDC--จำนวนประชากร Type 1,3 แยกเพศ,แยกรายหน่วยบริการ ###
Result :::
SELECT p.HOSPCODE,
SUM(CASE WHEN p.CID<>'' AND p.SEX='1' THEN 1 ELSE 0 END)AS men,
SUM(CASE WHEN p.CID<>'' AND p.SEX='2' THEN 1 ELSE 0 END)AS women,
SUM(CASE WHEN p.CID<>'' AND p.SEX='1' AND p.TYPEAREA='1' THEN 1 ELSE 0 END)AS men_type1,
SUM(CASE WHEN p.CID<>'' AND p.SEX='2' AND p.TYPEAREA='1' THEN 1 ELSE 0 END)AS women_type1,
SUM(CASE WHEN p.CID<>'' AND p.SEX='1' AND p.TYPEAREA='3' THEN 1 ELSE 0 END)AS men_type3,
SUM(CASE WHEN p.CID<>'' AND p.SEX='2' AND p.TYPEAREA='3' THEN 1 ELSE 0 END)AS women_type3,
SUM(CASE WHEN p.CID<>'' THEN 1 ELSE 0 END)AS total
FROM person p
WHERE p.DISCHARGE = '9' AND p.NATION = '099'
AND p.TYPEAREA IN('1','3')
GROUP BY p.HOSPCODE
UNION ALL
SELECT 'TOTAL',
SUM(CASE WHEN p.CID<>'' AND p.SEX='1' THEN 1 ELSE 0 END)AS men,
SUM(CASE WHEN p.CID<>'' AND p.SEX='2' THEN 1 ELSE 0 END)AS women,
SUM(CASE WHEN p.CID<>'' AND p.SEX='1' AND p.TYPEAREA='1' THEN 1 ELSE 0 END)AS men_type1,
SUM(CASE WHEN p.CID<>'' AND p.SEX='2' AND p.TYPEAREA='1' THEN 1 ELSE 0 END)AS women_type1,
SUM(CASE WHEN p.CID<>'' AND p.SEX='1' AND p.TYPEAREA='3' THEN 1 ELSE 0 END)AS men_type3,
SUM(CASE WHEN p.CID<>'' AND p.SEX='2' AND p.TYPEAREA='3' THEN 1 ELSE 0 END)AS women_type3,
SUM(CASE WHEN p.CID<>'' THEN 1 ELSE 0 END)AS total
FROM person p
WHERE p.DISCHARGE = '9' AND p.NATION = '099'
AND p.TYPEAREA IN('1','3')
ORDER BY HOSPCODE;
Result :::
