SELECT 
SURVEY_YEAR,
PF_NAME,
KIND,
coalesce(AMT,0) AS AMT
FROM (
SELECT 
2020 AS SURVEY_YEAR,
PF_CODE,
'小学校' AS KIND,
SUM(ELEMENTARY) AS AMT,
1 AS KEY
FROM
ENROLLMENT_STATUS
WHERE SURVEY_YEAR = 2020
GROUP BY PF_CODE

UNION ALL 

SELECT 
2020 AS SURVEY_YEAR,
PF_CODE,
'中学校' AS KIND,
SUM(MIDDLE) AS AMT,
2 AS KEY
FROM
ENROLLMENT_STATUS
WHERE SURVEY_YEAR = 2020
GROUP BY PF_CODE

UNION ALL 
SELECT
2020 AS SURVEY_YEAR,
PF_CODE,
'高校' AS KIND,
SUM(HIGH) AS AMT,
3 AS KEY
FROM
ENROLLMENT_STATUS
WHERE SURVEY_YEAR = 2020
GROUP BY PF_CODE
UNION ALL 
SELECT
2020 AS SURVEY_YEAR,
PF_CODE,
'短大' AS KIND,
SUM(JUNIOR_CLG) AS AMT,
4 AS KEY
FROM
ENROLLMENT_STATUS
WHERE SURVEY_YEAR = 2020
GROUP BY PF_CODE
UNION ALL 
SELECT
2020 AS SURVEY_YEAR,
PF_CODE,
'大学' AS KIND,
SUM(COLLEGE) AS AMT,
5 AS KEY
FROM
ENROLLMENT_STATUS
WHERE SURVEY_YEAR = 2020
GROUP BY PF_CODE
UNION ALL 
SELECT
2020 AS SURVEY_YEAR,
PF_CODE,
'大学院' AS KIND,
SUM(GRADUATE) AS AMT,
6 AS KEY
FROM
ENROLLMENT_STATUS
WHERE SURVEY_YEAR = 2020
GROUP BY PF_CODE
) total
INNER JOIN PREFECTURE ON PREFECTURE.PF_CODE = total.PF_CODE


ORDER BY PREFECTURE.PF_CODE ASC , total.KEY ASC