WITH TMP_TABLE AS(
    SELECT
        AGE,
        GENDER_CODE,
        SUM(
            CASE CATEGORY_CODE
                WHEN '10' THEN AVERAGE_VALUE
                ELSE 0
            END
        ) AS H_SUM,
        SUM(
            CASE CATEGORY_CODE
                WHEN '10' THEN 1
                ELSE 0
            END
        ) AS H_CNT,
        SUM(
            CASE CATEGORY_CODE
                WHEN '20' THEN AVERAGE_VALUE
                ELSE 0
            END
        ) AS W_SUM,
        SUM(
            CASE CATEGORY_CODE
                WHEN '20' THEN 1
                ELSE 0
            END
        ) AS W_CNT
    FROM
        SCHOOL_HEALTH
    WHERE
        SURVEY_YEAR = '2019'
    GROUP BY
        AGE,
        GENDER_CODE
)
SELECT
    SCHOOL_HEALTH.AGE,
    CASE SCHOOL_HEALTH.GENDER_CODE
        WHEN '20' THEN 'MALE'
        ELSE 'FEMALE'
    END AS GENDER_CODE,
    ROUND(H_SUM / CAST(H_CNT AS FLOAT), 1) AS H_AVG,
    CAST(
        ROUND(
            SUM(
                CASE
                    WHEN CATEGORY_CODE = '10' AND AVERAGE_VALUE >= ROUND(H_SUM / CAST(H_CNT AS FLOAT), 1) THEN 1
                    ELSE 0
                END
            ) * 100.0  / (H_CNT * 1.0), 1
        ) AS VARCHAR
    ) || '%' AS H_PER,
    ROUND(W_SUM / CAST(W_CNT AS FLOAT), 1) AS W_AVG,
    CAST(
        ROUND(
            SUM(
                CASE
                    WHEN CATEGORY_CODE = '20' AND AVERAGE_VALUE >= ROUND(W_SUM / CAST(W_CNT AS FLOAT), 1) THEN 1
                    ELSE 0
                END
            ) * 100.0  / (W_CNT * 1.0), 1
        ) AS W_PER
    ) || '%' AS W_PER
FROM
    SCHOOL_HEALTH
    INNER JOIN
        TMP_TABLE
        ON TMP_TABLE.AGE = SCHOOL_HEALTH.AGE
        AND TMP_TABLE.GENDER_CODE = SCHOOL_HEALTH.GENDER_CODE
GROUP BY
    SCHOOL_HEALTH.AGE,
    SCHOOL_HEALTH.GENDER_CODE
ORDER BY
    SCHOOL_HEALTH.AGE DESC,
    SCHOOL_HEALTH.GENDER_CODE DESC
-- SELECT
--     AGE,
--     GENDER_CODE,
--     H_SUM,
--     H_CNT,
--     W_SUM,
--     W_CNT
-- FROM
--     TMP_TABLE