WITH
AVERAGES AS
(SELECT
AGE,
GENDER_CODE,
CASE WHEN
GENDER_CODE = '20' THEN 'MALE'
WHEN
GENDER_CODE = '30' THEN 'FEMALE'
ELSE NULL
END AS GENDER,
ROUND(
SUM(
CASE 
WHEN CATEGORY_CODE = '10'
THEN AVERAGE_VALUE
ELSE 0
END)/
SUM(CASE WHEN
CATEGORY_CODE = '10'
THEN 1
ELSE 0 END),1) AS H_AVG,
ROUND(
SUM(
CASE 
WHEN CATEGORY_CODE = '20'
THEN AVERAGE_VALUE
ELSE 0
END)/
SUM(CASE WHEN
CATEGORY_CODE = '20'
THEN 1
ELSE 0 END),1) AS W_AVG
FROM
SCHOOL_HEALTH
WHERE SURVEY_YEAR = '2019'
GROUP BY 
AGE,GENDER
)
SELECT
t1.AGE,
GENDER,
H_AVG,
CAST(
ROUND(100.0*
SUM(CASE
WHEN AVERAGE_VALUE >= H_AVG AND CATEGORY_CODE = '10'
THEN 1
ELSE 0
END)/
SUM(
CASE WHEN CATEGORY_CODE = '10' THEN 1
ELSE 0
END)
,1) AS VARCHAR
)||'%'
AS H_PER,
W_AVG,
CAST(
ROUND(100.0*
SUM(CASE
WHEN AVERAGE_VALUE >= W_AVG AND CATEGORY_CODE = '20'
THEN 1
ELSE 0
END)/
SUM(CASE WHEN CATEGORY_CODE = '20' THEN 1
ELSE 0
END),1) AS VARCHAR) ||'%'
AS W_PER
FROM
SCHOOL_HEALTH t1
LEFT JOIN
AVERAGES t2
ON
t1.GENDER_CODE = t2.GENDER_CODE
AND
t1.AGE = t2.AGE
GROUP BY
t1.AGE,
GENDER
ORDER BY
t1.AGE DESC,
GENDER DESC