with avg as (
    select
        AGE,
        GENDER_CODE,
        round(avg(case CATEGORY_CODE 
                when '10' then AVERAGE_VALUE
            end),1) as H_AVG,
        round(avg(case CATEGORY_CODE 
                when '20' then AVERAGE_VALUE
            end),1) as W_AVG
    from SCHOOL_HEALTH
    where 
        SURVEY_YEAR = 2019
    group by 
        AGE,GENDER_CODE
)

select 
    avg.AGE,
    case avg.GENDER_CODE
        when '20' then 'MALE'
        when '30' then 'FEMALE'
    end as GENDER,
    H_AVG,
    round(
        cast(count(
            case
                when sh.CATEGORY_CODE = '10' and H_AVG <= sh.AVERAGE_VALUE then 1
            end
        )as real)/
        cast(count(
            case 
                when sh.CATEGORY_CODE = '10' then 1
            end
        ) as real) * 100,1
    ) || '%' as H_PER,
    W_AVG,
    round(
        cast(count(
            case 
                when sh.CATEGORY_CODE = '20' and W_AVG <= sh.AVERAGE_VALUE then 1
            end
        ) as real)/
        cast(count(
            case 
                when sh.CATEGORY_CODE = '20' then 1
            end
        ) as real) * 100,1
    ) || '%' as W_PER
from avg
left outer join SCHOOL_HEALTH as sh 
    on avg.AGE = sh.AGE and avg.GENDER_CODE = sh.GENDER_CODE and SURVEY_YEAR = 2019
group by
    avg.AGE,avg.GENDER_CODE
order by 
    avg.AGE desc,
    avg.GENDER_CODE desc;