select 
a.SURVEY_YEAR as  SV_YEAR,
b.PF_NAME as PREFECTURE,
a.KIND,
SUM(a.AMT) as AMT
from 
(select  SURVEY_YEAR,PF_CODE,CASE WHEN ELEMENTARY > 0 THEN '小学校'
             when MIDDLE > 0 THEN '中学校'
             when HIGH > 0 THEN '高校'
             when JUNIOR_CLG > 0 THEN '短大'
             when COLLEGE > 0 THEN '大学'
             ELSE null END AS KIND,
        CASE WHEN ELEMENTARY > 0 THEN '1'
             when MIDDLE > 0 THEN '2'
             when HIGH > 0 THEN '3'
             when JUNIOR_CLG > 0 THEN '4'
             when COLLEGE > 0 THEN '5'
             ELSE '9' END AS KIND_cd,
        CASE WHEN ELEMENTARY > 0 THEN ELEMENTARY
             when MIDDLE > 0 THEN MIDDLE
             when HIGH > 0 THEN HIGH
             when JUNIOR_CLG > 0 THEN JUNIOR_CLG
             when COLLEGE > 0 THEN COLLEGE
             ELSE '9' END AS AMT
             from ENROLLMENT_STATUS where SURVEY_YEAR ='2020') a
inner join PREFECTURE b on a.PF_CODE = b.PF_CODE
where KIND is not null
group by a.SURVEY_YEAR, b.PF_NAME,a.KIND
ORDER BY a.PF_CODE,KIND_cd