select
    SURVEY_YEAR as SV_YEAR
    , pref.PF_NAME as "PREFECTURE"
    , KIND
    , AMT
from
(
    select
        SURVEY_YEAR
        , PF_CODE
        , '小学校' as KIND
        , SUM(ELEMENTARY) as AMT
        , 0 as rank
    from
        ENROLLMENT_STATUS
    where
        SURVEY_YEAR = 2020
    group by
        1, 2, 3, 5
    UNION ALL
    select
        SURVEY_YEAR
        , PF_CODE
        , '中学校' as KIND
        , SUM(MIDDLE) as AMT
        , 1 as rank
    from
        ENROLLMENT_STATUS
    where
        SURVEY_YEAR = 2020
    group by
        1, 2, 3, 5
    UNION ALL
    select
        SURVEY_YEAR
        , PF_CODE
        , '高校' as KIND
        , SUM(HIGH) as AMT
        , 2 as rank
    from
        ENROLLMENT_STATUS
    where
        SURVEY_YEAR = 2020
    group by
        1, 2, 3, 5
    UNION ALL
    select
        SURVEY_YEAR
        , PF_CODE
        , '短大' as KIND
        , SUM(JUNIOR_CLG) as AMT
        , 3 as rank
    from
        ENROLLMENT_STATUS
    where
        SURVEY_YEAR = 2020
    group by
        1, 2,3, 5
    UNION ALL
    select
        SURVEY_YEAR
        , PF_CODE
        , '大学' as KIND
        , SUM(COLLEGE) as AMT
        , 4 as rank
    from
        ENROLLMENT_STATUS
    where
        SURVEY_YEAR = 2020
    group by
        1, 2, 3, 5
    UNION ALL
    select
        SURVEY_YEAR
        , PF_CODE
        , '大学院' as KIND
        , SUM(GRADUATE) as AMT
        , 5 as rank
    from
        ENROLLMENT_STATUS
    where
        SURVEY_YEAR = 2020
    group by
        1, 2,3, 5) tbl
left join
    PREFECTURE pref
on
    tbl.PF_CODE = pref.PF_CODE
where
    AMT IS NOT NULL
order by
    pref.PF_CODE
    , "rank";