select
    a.SURVEY_YEAR as SV_YEAR
    ,p.PF_NAME as PREFECTURE
    ,a.KIND as KIND
    ,a.AMT as AMT
from (
    select
        SURVEY_YEAR
        ,PF_CODE
        ,'小学校' as KIND
        ,sum(ELEMENTARY) as AMT
        ,1 as sortkey
    from ENROLLMENT_STATUS
    where
        ELEMENTARY is not null
    group by
        SURVEY_YEAR
        ,PF_CODE
    
    union all

    select
        SURVEY_YEAR
        ,PF_CODE
        ,'中学校' as KIND
        ,sum(MIDDLE) as AMT
        ,2 as sortkey
    from ENROLLMENT_STATUS
    where
        MIDDLE is not null
    group by
        SURVEY_YEAR
        ,PF_CODE    

    union all

    select
        SURVEY_YEAR
        ,PF_CODE
        ,'高校' as KIND
        ,sum(HIGH) as AMT
        ,3 as sortkey
    from ENROLLMENT_STATUS
    where
        HIGH is not null
    group by
        SURVEY_YEAR
        ,PF_CODE 

    union all

    select
        SURVEY_YEAR
        ,PF_CODE
        ,'短大' as KIND
        ,sum(JUNIOR_CLG) as AMT
        ,4 as sortkey
    from ENROLLMENT_STATUS
    where
        JUNIOR_CLG is not null
    group by
        SURVEY_YEAR
        ,PF_CODE 
        
    union all

    select
        SURVEY_YEAR
        ,PF_CODE
        ,'大学' as KIND
        ,sum(COLLEGE) as AMT
        ,5 as sortkey
    from ENROLLMENT_STATUS
    where
        COLLEGE is not null
    group by
        SURVEY_YEAR
        ,PF_CODE 
        
    union all

    select
        SURVEY_YEAR
        ,PF_CODE
        ,'大学院' as KIND
        ,sum(GRADUATE) as AMT
        ,6 as sortkey
    from ENROLLMENT_STATUS
    where
        GRADUATE is not null
    group by
        SURVEY_YEAR
        ,PF_CODE 
) a
left join PREFECTURE p
    on a.PF_CODE = p.PF_CODE
where
    a.SURVEY_YEAR = 2020
order by
    a.PF_CODE asc
    ,a.sortkey asc