SELECT A.SURVEY_YEAR as SV_YEAR
       ,B.PF_NAME    as PREFECTURE
       ,A.name as KIND
       ,A.su as AMT
       
FROM (
      SELECT PF_CODE
             ,1 as num
             ,SURVEY_YEAR
             ,"小学校" as name
             ,SUM(ELEMENTARY) as su
        FROM ENROLLMENT_STATUS
       WHERE SURVEY_YEAR = 2020
       GROUP BY
             PF_CODE
             ,SURVEY_YEAR
             
       UNION
       
       SELECT PF_CODE
             ,2 as num
             ,SURVEY_YEAR
             ,"中学校" as name
             ,SUM(MIDDLE) as su
        FROM ENROLLMENT_STATUS
       WHERE SURVEY_YEAR = 2020
       GROUP BY
             PF_CODE
             ,SURVEY_YEAR
        
        UNION
        
        SELECT PF_CODE
             ,3 as num
             ,SURVEY_YEAR
             ,"高校" as name
             ,SUM(HIGH) as su
        FROM ENROLLMENT_STATUS
       WHERE SURVEY_YEAR = 2020
       GROUP BY
             PF_CODE
             ,SURVEY_YEAR
             
        UNION
        
        SELECT PF_CODE
             ,4 as num
             ,SURVEY_YEAR
             ,"短大" as name
             ,SUM(JUNIOR_CLG) as su
        FROM ENROLLMENT_STATUS
       WHERE SURVEY_YEAR = 2020
       GROUP BY
             PF_CODE
             ,SURVEY_YEAR
             
        UNION
        
        SELECT PF_CODE
             ,5 as num
             ,SURVEY_YEAR
             ,"大学" as name
             ,SUM(COLLEGE) as su
        FROM ENROLLMENT_STATUS
       WHERE SURVEY_YEAR = 2020
       GROUP BY
             PF_CODE
             ,SURVEY_YEAR
             
        UNION
        
        SELECT PF_CODE
             ,6 as num
             ,SURVEY_YEAR
             ,"大学院" as name
             ,SUM(GRADUATE) as su
        FROM ENROLLMENT_STATUS
       WHERE SURVEY_YEAR = 2020
       GROUP BY
             PF_CODE
             ,SURVEY_YEAR
     ) as A
     
JOIN PREFECTURE as B
  ON A.PF_CODE = B.PF_CODE
  
WHERE A.su is not null

ORDER BY A.PF_CODE
         ,A.num