with
es as (
    select
        *
    from
        ENROLLMENT_STATUS
    where
        survey_year = "2020"
)

select
    survey_year as SV_YEAR,
    pf_name as PREFECTURE,
    KIND,
    AMT

from (
    select
        survey_year,
        pf_code,
        1 as num,
        "小学校" as KIND,
        sum(elementary) as AMT
    from 
        es 
    group by
        survey_year,
        pf_code
    
    union all
    select
        survey_year,
        pf_code,
        2 as num,

        "中学校" as KIND,
        sum(middle) as AMT
    from 
        es 
    group by
        survey_year,
        pf_code

    union all
    
    select
        survey_year,
        pf_code,
        3 as num,
        "高校" as KIND,
        sum(high) as AMT
    from 
        es 
    group by
        survey_year,
        pf_code

    union all

    select
        survey_year,
        pf_code,
        4 as num,
        "短大" as KIND,
        sum(junior_clg) as AMT
    from 
        es 
    group by
        survey_year,
        pf_code

    union all

    select
        survey_year,
        pf_code,
        5 as num,
        "大学" as KIND,
        sum(college) as AMT
    from 
        es 
    group by
        survey_year,
        pf_code
    union all

    select
        survey_year,
        pf_code,
        6 as num,
        "大学院" as KIND,
        sum(graduate) as AMT
    from 
        es 
    group by
        survey_year,
        pf_code

) as ee
left outer join
    prefecture
on
    ee.pf_code = prefecture.pf_code
where
    amt is not null
order by
    ee.pf_code,
    num