WITH AVG_TBL AS (
SELECT ROUND((CAST(COUNT(PF_CODE) AS REAL)/CAST(AVERAGE.PF_COUNT AS REAL))*100,1)||'%' PER ,SH.AGE,SH.GENDER_CODE,SH.CATEGORY_CODE,AVERAGE.AVG FROM SCHOOL_HEALTH SH
INNER JOIN (
SELECT
AGE,GENDER_CODE,CATEGORY_CODE,
ROUND(AVG(AVERAGE_VALUE),1) AVG,
COUNT(PF_CODE) PF_COUNT
FROM SCHOOL_HEALTH
WHERE SURVEY_YEAR=2019 AND AVERAGE_VALUE IS NOT NULL
GROUP BY CATEGORY_CODE, GENDER_CODE, AGE
) AVERAGE ON SH.AGE=AVERAGE.AGE AND SH.GENDER_CODE=AVERAGE.GENDER_CODE AND SH.CATEGORY_CODE=AVERAGE.CATEGORY_CODE
WHERE SH.AVERAGE_VALUE>=AVG
GROUP BY SH.AGE,SH.GENDER_CODE,SH.CATEGORY_CODE
)
SELECT H.AGE AGE,
CASE
WHEN H.GENDER_CODE=20 THEN 'MALE'
WHEN H.GENDER_CODE=30 THEN 'FEMALE'
END GENDER,
H_AVG,H_PER,W_AVG,W_PER
FROM (SELECT AGE,GENDER_CODE,AVG H_AVG,PER H_PER FROM AVG_TBL WHERE CATEGORY_CODE=10) H
INNER JOIN (SELECT AGE,GENDER_CODE,AVG W_AVG,PER W_PER FROM AVG_TBL WHERE CATEGORY_CODE=20) W ON H.AGE=W.AGE AND H.GENDER_CODE=W.GENDER_CODE
ORDER BY H.AGE DESC,H.GENDER_CODE DESC