with al__ as(
    select
        t.SURVEY_YEAR as SV_YEAR,
        t.PF_CODE as p_code,
        m.PF_NAME as PREFECTURE,
        '小学校' as KIND,
        '1' as s_code,
        sum(t.ELEMENTARY) as AMT
    from
        ENROLLMENT_STATUS as t
    inner join
        PREFECTURE as m on t.PF_CODE = m.PF_CODE
    where
        t.ELEMENTARY is not Null
        and
        t.SURVEY_YEAR = '2020'
    group by
        t.PF_CODE

    union all

    select
        t.SURVEY_YEAR as SV_YEAR,
        t.PF_CODE as p_code,
        m.PF_NAME as PREFECTURE,
        '中学校' as KIND,
        '2' as s_code,
        sum(t.MIDDLE) as AMT
    from
        ENROLLMENT_STATUS as t
    inner join
        PREFECTURE as m on t.PF_CODE = m.PF_CODE
    where
        t.MIDDLE is not Null
        and
        t.SURVEY_YEAR = '2020'
    group by

        t.PF_CODE

    union all

    select
        t.SURVEY_YEAR as SV_YEAR,
        t.PF_CODE as p_code,
        m.PF_NAME as PREFECTURE,
        '高校' as KIND,
        '3' as s_code,
        sum(t.HIGH) as AMT
    from
        ENROLLMENT_STATUS as t
    inner join
        PREFECTURE as m on t.PF_CODE = m.PF_CODE
    where
        t.HIGH is not Null
        and
        t.SURVEY_YEAR = '2020'
    group by

        t.PF_CODE

    union all

    select
        t.SURVEY_YEAR as SV_YEAR,
        t.PF_CODE as p_code,
        m.PF_NAME as PREFECTURE,
        '短大' as KIND,
        '4' as s_code,
        sum(t.JUNIOR_CLG) as AMT
    from
        ENROLLMENT_STATUS as t
    inner join
        PREFECTURE as m on t.PF_CODE = m.PF_CODE
    where
        t.JUNIOR_CLG is not Null
        and
        t.SURVEY_YEAR = '2020'
    group by

        t.PF_CODE

    union all

    select
        t.SURVEY_YEAR as SV_YEAR,
        t.PF_CODE as p_code,
        m.PF_NAME as PREFECTURE,
        '大学' as KIND,
        '5' as s_code,
        sum(t.COLLEGE) as AMT
    from
        ENROLLMENT_STATUS as t
    inner join
        PREFECTURE as m on t.PF_CODE = m.PF_CODE
    where
        t.COLLEGE is not Null
        and
        t.SURVEY_YEAR = '2020'
    group by

        t.PF_CODE

    union all

    select
        t.SURVEY_YEAR as SV_YEAR,
        t.PF_CODE as p_code,
        m.PF_NAME as PREFECTURE,
        '大学院' as KIND,
        '6' as s_code,
        sum(t.GRADUATE) as AMT
    from
        ENROLLMENT_STATUS as t
    inner join
        PREFECTURE as m on t.PF_CODE = m.PF_CODE
    where
        t.GRADUATE is not Null
        and
        t.SURVEY_YEAR = '2020'
    group by
        t.PF_CODE
)
select
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
from
    al__
order by
    p_code,
    s_code
;