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テーブルを合成  
    ,sub_H as (
        select * from sub
        union all
        select * from sub3
        )
    ,sub_W as (
        select * from sub2
        union all
        select * from sub4
        )
--select * from sub_H  
    
select  
    sub_H.AGE as AGE
    ,(case when sub_H.GENDER_CODE = '20'
        Then  'MALE'
        when sub_H.GENDER_CODE = '30'
        Then 'FEMALE' 
        else null end) as GENDER
    ,sub_H.Ave as H_AVG
    ,sub_H.Per || '%' as H_PER
    ,sub_W.Ave as W_AVG
    ,sub_W.Per || '%' as W_PER
from 
    sub_H
    inner join sub_W
    on sub_H.AGE = sub_W.AGE
    and sub_H.GENDER_CODE = sub_W.GENDER_CODE
--group by sub_H.AGE,sub_H.GENDER_CODE
order by
    AGE desc
    ,sub_H.GENDER_CODE desc