SELECT SURVEY_YEAR AS SV_YEAR
       ,PF_NAME AS PREFECTURE
       ,KIND AS KIND
       ,SUM(AMT) AS AMT
  FROM (SELECT PF_CODE 
        ,ELEMENTARY AS AMT
        ,'小学校'AS KIND
        ,SURVEY_YEAR
        ,1 AS CODE
   FROM ENROLLMENT_STATUS
  WHERE SURVEY_YEAR = 2020
  AND ELEMENTARY IS NOT NULL
UNION ALL
 SELECT PF_CODE 
        ,MIDDLE AS AMT
        ,'中学校'AS KIND
        ,SURVEY_YEAR
        ,2 AS CODE
   FROM ENROLLMENT_STATUS
  WHERE SURVEY_YEAR = 2020
  AND MIDDLE IS NOT NULL
UNION ALL  
 SELECT PF_CODE 
        ,HIGH AS AMT
        ,'高校'AS KIND
        ,SURVEY_YEAR
        ,3 AS CODE
   FROM ENROLLMENT_STATUS
  WHERE SURVEY_YEAR = 2020
  AND HIGH IS NOT NULL
UNION ALL  
  SELECT PF_CODE 
        ,JUNIOR_CLG AS AMT
        ,'短大'AS KIND
        ,SURVEY_YEAR
        ,4 AS CODE
   FROM ENROLLMENT_STATUS
  WHERE SURVEY_YEAR = 2020
  AND JUNIOR_CLG IS NOT NULL
UNION ALL  
  SELECT PF_CODE 
        ,COLLEGE AS AMT
        ,'大学'AS KIND
        ,SURVEY_YEAR
        ,5 AS CODE
   FROM ENROLLMENT_STATUS
  WHERE SURVEY_YEAR = 2020
  AND COLLEGE IS NOT NULL
UNION ALL  
  SELECT PF_CODE 
        ,GRADUATE AS AMT
        ,'大学院'AS KIND
        ,SURVEY_YEAR
        ,6 AS CODE
   FROM ENROLLMENT_STATUS
  WHERE SURVEY_YEAR = 2020
  AND GRADUATE IS NOT NULL) ENROLLMENT_STATUS
  JOIN PREFECTURE
    ON ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
 GROUP BY ENROLLMENT_STATUS.PF_CODE,KIND
 ORDER BY ENROLLMENT_STATUS.PF_CODE DESC,CODE