with ELEMENTARY as (
    select
        SURVEY_YEAR,
        PF_CODE,
        "小学校" as KIND,
        sum(ELEMENTARY) as AMT
    from
        ENROLLMENT_STATUS
    group by
        1,2,3
),
MIDDLE as (
    select
        SURVEY_YEAR,
        PF_CODE,
        "中学校" as KIND,
        sum(MIDDLE) as AMT
    from
        ENROLLMENT_STATUS
    group by
        1,2,3
),
HIGH as (
    select
        SURVEY_YEAR,
        PF_CODE,
        "高校" as KIND,
        sum(HIGH) as AMT
    from
        ENROLLMENT_STATUS
    group by
        1,2,3
),
JUNIOR_CLG as (
    select
        SURVEY_YEAR,
        PF_CODE,
        "短大" as KIND,
        sum(JUNIOR_CLG) as AMT
    from
        ENROLLMENT_STATUS
    group by
        1,2,3
),
COLLEGE as (
    select
        SURVEY_YEAR,
        PF_CODE,
        "大学" as KIND,
        sum(COLLEGE) as AMT
    from
        ENROLLMENT_STATUS
    group by
        1,2,3
),
GRADUATE as (
    select
        SURVEY_YEAR,
        PF_CODE,
        "大学院" as KIND,
        sum(GRADUATE) as AMT
    from
        ENROLLMENT_STATUS
    group by
        1,2,3
)
select
    e.SURVEY_YEAR as SV_YEAR,
    p.PF_NAME as PREFECTURE,
    e.KIND,
    sum(e.AMT) as AMT
from
    (
        select * from ELEMENTARY
        union all
        select * from MIDDLE
        union all
        select * from HIGH
        union all
        select * from JUNIOR_CLG
        union all
        select * from COLLEGE
        union all
        select * from GRADUATE
    ) as e
inner join
    PREFECTURE as p
    on e.PF_CODE = p.PF_CODE
where
    e.SURVEY_YEAR = 2020
group by
    1,2,3
having
    AMT is not null
order by
    p.PF_CODE asc,
    case kind
        when "小学校" then 0
        when "中学校" then 1
        when "高校" then 2
        when "短大" then 3
        when "大学" then 4
        when "大学院" then 5
    end asc