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,
        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 VARCHAR
        ) || '%' 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
    WHERE
        SURVEY_YEAR = '2019'
    GROUP BY
        SCHOOL_HEALTH.AGE,
        SCHOOL_HEALTH.GENDER_CODE
    ORDER BY
        SCHOOL_HEALTH.AGE DESC,
        SCHOOL_HEALTH.GENDER_CODE DESC