with
    M_H_Ave as (
        select
            SURVEY_YEAR
            ,PF_CODE
            ,CATEGORY_CODE
            ,GENDER_CODE
            ,AGE
            ,AVERAGE_VALUE
            ,round(AVG(AVERAGE_VALUE)OVER(partition by AGE),1) as Ave
        from SCHOOL_HEALTH
        where 
            GENDER_CODE = '20'
            and CATEGORY_CODE = '10'
            and SURVEY_YEAR = '2019'
        order by AGE,GENDER_CODE,CATEGORY_CODE
        )
    ,sub as (
        select
            SURVEY_YEAR
            ,CATEGORY_CODE
            ,GENDER_CODE
            ,AGE
            ,Ave
            ,round(cast(count(case when AVERAGE_VALUE>=Ave then 1 end)as float)*100/count(Ave),1) as Per
        from M_H_Ave
        group by AGE
        )
--select * from sub

    ,M_W_Ave as (
        select
            SURVEY_YEAR
            ,PF_CODE
            ,CATEGORY_CODE
            ,GENDER_CODE
            ,AGE
            ,AVERAGE_VALUE
            ,round(AVG(AVERAGE_VALUE)OVER(partition by AGE),1) as Ave
        from SCHOOL_HEALTH
        where
            GENDER_CODE = '20'
            and CATEGORY_CODE = '20'
            and SURVEY_YEAR = '2019'
        )
    ,sub2 as (
        select
            SURVEY_YEAR
            ,CATEGORY_CODE
            ,GENDER_CODE
            ,AGE
            ,Ave
            ,round(cast(count(case when AVERAGE_VALUE>=Ave then 1 end)as float)*100/count(Ave),1) as Per
        from M_W_Ave
        group by AGE
        )
    ,F_H_Ave as (
        select
            SURVEY_YEAR
            ,PF_CODE
            ,CATEGORY_CODE
            ,GENDER_CODE
            ,AGE
            ,AVERAGE_VALUE
            ,round(AVG(AVERAGE_VALUE)OVER(partition by AGE),1) as Ave
        from SCHOOL_HEALTH
        where
            GENDER_CODE = '30'
            and CATEGORY_CODE = '10'
            and SURVEY_YEAR = '2019'
        )
    ,sub3 as (
        select
            SURVEY_YEAR
            ,CATEGORY_CODE
            ,GENDER_CODE
            ,AGE
            ,Ave
            ,round(cast(count(case when AVERAGE_VALUE>=Ave then 1 end)as float)*100/count(Ave),1) as Per
        from F_H_Ave
        group by AGE
        )
    ,F_W_Ave as (
        select
            SURVEY_YEAR
            ,PF_CODE
            ,CATEGORY_CODE
            ,GENDER_CODE
            ,AGE
            ,AVERAGE_VALUE
            ,round(AVG(AVERAGE_VALUE)OVER(partition by AGE),1) as Ave
        from SCHOOL_HEALTH
        where
            GENDER_CODE = '30'
            and CATEGORY_CODE = '20'
            and SURVEY_YEAR = '2019'
        )
    ,sub4 as (
        select
            SURVEY_YEAR
            ,CATEGORY_CODE
            ,GENDER_CODE
            ,AGE
            ,Ave
            ,round(cast(count(case when AVERAGE_VALUE>=Ave then 1 end)as float)*100/count(Ave),1) as Per
        from F_W_Ave
        group by AGE
        )
      
    --全subテーブルを合成  
    select * from sub
    union all
    select * from sub2
    ((union all
    select * from sub3)
    union all
    select * from sub4)
    
    
select *    
    -- AGE as AGE
    -- ,GENDER_CODE('20'は 'MALE' 、'30'は 'FEMALE' と表示する) as GENDER
    -- ,身長の平均値 as H_AVG
    -- ,身長平均値以上の割合 as H_PER
    -- ,体重の平均値 as W_AVG
    -- ,体重平均値以上の割合 as W_PER
from 
    sub
order by
    AGE desc
    ,GENDER_CODE desc