--6種類をwithで作成する？
--小学校
WITH elem AS
(
 SELECT
  SURVEY_YEAR AS SV_YEAR,
  PF_NAME AS PREFECTURE,
  '小学校' AS KIND,
  PF_CODE,
  SUM(ELEMENTARY) AS AMT
 FROM
  ENROLLMENT_STATUS
 LEFT JOIN
  PREFECTURE
 USING
  (PF_CODE)
 WHERE
  SV_YEAR = 2020
 GROUP BY
  SURVEY_YEAR,PF_NAME,PF_CODE
),

--中学校
middle AS
(
 SELECT
  SURVEY_YEAR AS SV_YEAR,
  PF_NAME AS PREFECTURE,
  '中学校' AS KIND,
  PF_CODE,
  SUM(MIDDLE) AS AMT
 FROM
  ENROLLMENT_STATUS
 LEFT JOIN
  PREFECTURE
 USING
  (PF_CODE)
 WHERE
  SV_YEAR = 2020
 AND
  MIDDLE IS NOT NULL
 GROUP BY
  SURVEY_YEAR,PF_NAME,PF_CODE
),

--高校
high AS
(
 SELECT
  SURVEY_YEAR AS SV_YEAR,
  PF_NAME AS PREFECTURE,
  '高校' AS KIND,
  PF_CODE,
  SUM(HIGH) AS AMT
 FROM
  ENROLLMENT_STATUS
 LEFT JOIN
  PREFECTURE
 USING
  (PF_CODE)
 WHERE
  SV_YEAR = 2020
 AND
  HIGH IS NOT NULL
 GROUP BY
  SURVEY_YEAR,PF_NAME,PF_CODE
),
--短大
junior_clg AS
(
 SELECT
  SURVEY_YEAR AS SV_YEAR,
  PF_NAME AS PREFECTURE,
  '短大' AS KIND,
  PF_CODE,
  SUM(junior_clg) AS AMT
 FROM
  ENROLLMENT_STATUS
 LEFT JOIN
  PREFECTURE
 USING
  (PF_CODE)
 WHERE
  SV_YEAR = 2020
 AND
  JUNIOR_CLG IS NOT NULL
 GROUP BY
  SURVEY_YEAR,PF_NAME,PF_CODE
),
--大学
college AS
(
 SELECT
  SURVEY_YEAR AS SV_YEAR,
  PF_NAME AS PREFECTURE,
  '大学' AS KIND,
  PF_CODE,
  SUM(college) AS AMT
 FROM
  ENROLLMENT_STATUS
 LEFT JOIN
  PREFECTURE
 USING
  (PF_CODE)
 WHERE
  SV_YEAR = 2020
 AND
  college IS NOT NULL
 GROUP BY
  SURVEY_YEAR,PF_NAME,PF_CODE
),
--大学院
graduate AS
(
 SELECT
  SURVEY_YEAR AS SV_YEAR,
  PF_NAME AS PREFECTURE,
  '大学院' AS KIND,
  PF_CODE,
  SUM(graduate) AS AMT
 FROM
  ENROLLMENT_STATUS
 LEFT JOIN
  PREFECTURE
 USING
  (PF_CODE)
 WHERE
  SV_YEAR = 2020
 AND
  graduate IS NOT NULL
 GROUP BY
  SURVEY_YEAR,PF_NAME,PF_CODE
),

--UNION後
uni_data AS
(
SELECT
 *
FROM
 elem
UNION ALL
SELECT
 *
FROM
 middle
UNION ALL
SELECT
 *
FROM
 high
UNION ALL
SELECT
 *
FROM
 junior_clg
UNION ALL
SELECT
 *
FROM
 college
UNION ALL
SELECT
 *
FROM
 graduate
)

SELECT
 SV_YEAR,
 PREFECTURE,
 KIND,
 AMT
FROM
 uni_data
ORDER BY
 PF_CODE