SELECT
SURVEY_YEAR AS SV_YEAR
,p.PF_NAME AS PREFECTURE
, CASE
  WHEN IFNULL(ELEMENTARY, 0) > 0 THEN '小学校'
  WHEN IFNULL(MIDDLE, 0) > 0 THEN '中学校'
  WHEN IFNULL(HIGH, 0) > 0 THEN '高校'
  WHEN IFNULL(JUNIOR_CLG, 0) > 0 THEN '短大'
  WHEN IFNULL(COLLEGE, 0) > 0 THEN '大学'
  WHEN IFNULL(GRADUATE, 0) > 0 THEN '大学院'
  END AS KIND

, SUM(CASE
  WHEN IFNULL(ELEMENTARY, 0) > 0 THEN ELEMENTARY
  WHEN IFNULL(MIDDLE, 0) > 0 THEN MIDDLE
  WHEN IFNULL(HIGH, 0) > 0 THEN HIGH
  WHEN IFNULL(JUNIOR_CLG, 0) > 0 THEN JUNIOR_CLG
  WHEN IFNULL(COLLEGE, 0) > 0 THEN COLLEGE
  WHEN IFNULL(GRADUATE, 0) > 0 THEN GRADUATE
  END) AS AMT
  
, CASE
  WHEN IFNULL(ELEMENTARY, 0) > 0 THEN 1
  WHEN IFNULL(MIDDLE, 0) > 0 THEN 2
  WHEN IFNULL(HIGH, 0) > 0 THEN 3
  WHEN IFNULL(JUNIOR_CLG, 0) > 0 THEN 4
  WHEN IFNULL(COLLEGE, 0) > 0 THEN 5
  WHEN IFNULL(GRADUATE, 0) > 0 THEN 6
  END AS t


FROM ENROLLMENT_STATUS e
INNER JOIN PREFECTURE p ON p.PF_CODE = e.PF_CODE
WHERE SURVEY_YEAR = 2020
GROUP BY e.PF_CODE, KIND
ORDER BY e.PF_CODE ASC, t
;