SELECT 
 SURVEY_YEAR as SV_YEAR
 ,PF_NAME as PREFECTURE 
 ,CASE WHEN ELEMENTARY > 0 and age >= 6 and age <=12 THEN '小学校' WHEN MIDDLE > 0 and age >= 12 and age <= 15 THEN '中学校' WHEN HIGH > 0 THEN and age >= 15 and age >=18 '高校' WHEN JUNIOR_CLG > 0 THEN '短大' WHEN COLLEGE > 0 THEN '大学' WHEN GRADUATE >0 THEN '大学院' END  as KIND
 ,SUM(CASE WHEN ELEMENTARY > 0 and age >= 6 and age <=12 THEN ELEMENTARY WHEN MIDDLE > 0 and age >= 12 and age <= 15 THEN MIDDLE WHEN HIGH > 0 and age >= 15 >=18 and age THEN HIGH WHEN JUNIOR_CLG > 0 THEN JUNIOR_CLG WHEN COLLEGE > 0 THEN COLLEGE WHEN GRADUATE >0 THEN GRADUATE END) as AMT
from 
 PREFECTURE pre 
inner join ENROLLMENT_STATUS enr
  on pre.PF_CODE = enr.PF_CODE
WHERE 
 SURVEY_YEAR = 2020
 AND ELEMENTARY is not null
 AND MIDDLE is not null
 AND HIGH is not null
 AND JUNIOR_CLG is not null
 AND COLLEGE is not null
 AND GRADUATE is not null
GROUP BY
 SV_YEAR,PREFECTURE,KIND
ORDER BY
 pre.PF_CODE asc, age asc;