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,
    ROUND(H_SUM / CAST(H_CNT AS FLOAT), 1) AS H_AVG,
    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 H_PER,
    ROUND(W_SUM / CAST(W_CNT AS FLOAT), 1) AS W_AVG,
    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
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