select
    sum(heikin_ijou_flg) as per
    ,count(heikin_ijou_flg) 
    ,age
    ,gender_code
    ,category_code
from
(
select
    case
        when
        average_value >= zenkoku_ave_value
        then
            1
        else 0
    end as heikin_ijou_flg
    ,pf_code
    ,age
    ,gender_code
    ,category_code
from
(
select
    sh.pf_code
    ,sh.age
    ,sh.gender_code 
    ,sh.average_value
    ,sh.category_code
    ,zenkoku.zenkoku_ave_value
from
    school_health sh
    inner join
    (
    
    select
        category_code
        ,age
        ,gender_code
        ,avg(average_value) as zenkoku_ave_value
    from
        school_health
    where
        survey_year = '2019'
    group by 
        category_code 
        ,age
        ,gender_code
    ) zenkoku
    ON sh.age = zenkoku.age
    and sh.gender_code = zenkoku.gender_code
    and sh.category_code = zenkoku.category_code
where
    sh.survey_year = '2019'
)
)
group by 
    age
    ,gender_code
    ,category_code