WITH
EL as (SELECT 
SURVEY_YEAR,
PF_CODE,
SUM(ELEMENTARY) AS AMT,
'小学校' AS KIND,
1 AS KIND_NUM
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY SURVEY_YEAR, PF_CODE, KIND, KIND_NUM),
MIDDLE as (SELECT 
SURVEY_YEAR,
PF_CODE,
SUM(MIDDLE) AS AMT,
'中学校' AS KIND,
2 AS KIND_NUM
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY SURVEY_YEAR, PF_CODE, KIND, KIND_NUM),
HIGH as (SELECT 
SURVEY_YEAR,
PF_CODE,
SUM(HIGH) AS AMT,
'高校' AS KIND,
3 AS KIND_NUM
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY SURVEY_YEAR, PF_CODE, KIND, KIND_NUM),
JU_CO as (SELECT 
SURVEY_YEAR,
PF_CODE,
SUM(JUNIOR_CLG) AS AMT,
'短大' AS KIND,
4 AS KIND_NUM
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY SURVEY_YEAR, PF_CODE, KIND, KIND_NUM),
CLG as (SELECT 
SURVEY_YEAR,
PF_CODE,
SUM(COLLEGE) AS AMT,
'大学' AS KIND,
5 AS KIND_NUM
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY SURVEY_YEAR, PF_CODE, KIND, KIND_NUM),

GRADUATE as (SELECT 
SURVEY_YEAR,
PF_CODE,
SUM(GRADUATE) AS AMT,
'大学院' AS KIND,
6 AS KIND_NUM
FROM ENROLLMENT_STATUS
WHERE SURVEY_YEAR = '2020'
GROUP BY SURVEY_YEAR, PF_CODE, KIND, KIND_NUM)
SELECT
SURVEY_YEAR AS SV_YEAR,
PF_NAME AS PREFECTURE,
KIND,
AMT
FROM(
SELECT * FROM EL
UNION SELECT * FROM MIDDLE
UNION SELECT * FROM HIGH
UNION SELECT * FROM JU_CO
UNION SELECT * FROM CLG
UNION SELECT * FROM GRADUATE) AS tbl
JOIN PREFECTURE as pf
ON tbl.PF_CODE = pf.PF_CODE
WHERE AMT IS NOT NULL
ORDER BY tbl.PF_CODE, KIND_NUM