SELECT
SV_YEAR, PREFECTURE, KIND, AMT
FROM
(
SELECT
e.SURVEY_YEAR AS SV_YEAR
,p.PF_CODE AS CODE
,p.PF_NAME AS PREFECTURE
,'小学校' AS KIND
,0 AS KIND_NUM
,SUM(e.ELEMENTARY) AS AMT 
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020
GROUP BY p.PF_NAME
UNION
SELECT
e.SURVEY_YEAR AS SV_YEAR
,p.PF_CODE AS CODE
,p.PF_NAME AS PREFECTURE
,'中学校' AS KIND
,1 AS KIND_NUM
,SUM(e.MIDDLE) AS AMT 
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020
GROUP BY p.PF_NAME
UNION
SELECT
e.SURVEY_YEAR AS SV_YEAR
,p.PF_CODE AS CODE
,p.PF_NAME AS PREFECTURE
,'高校' AS KIND
,2 AS KIND_NUM
,SUM(e.HIGH) AS AMT 
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020
GROUP BY p.PF_NAME
UNION
SELECT
e.SURVEY_YEAR AS SV_YEAR
,p.PF_CODE AS CODE
,p.PF_NAME AS PREFECTURE
,'短大' AS KIND
,3 AS KIND_NUM
,SUM(e.JUNIOR_CLG) AS AMT 
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020
GROUP BY p.PF_NAME
UNION
SELECT
e.SURVEY_YEAR AS SV_YEAR
,p.PF_CODE AS CODE
,p.PF_NAME AS PREFECTURE
,'大学' AS KIND
,4 AS KIND_NUM
,SUM(e.COLLEGE) AS AMT 
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020
GROUP BY p.PF_NAME
UNION
SELECT
e.SURVEY_YEAR AS SV_YEAR
,p.PF_CODE AS CODE
,p.PF_NAME AS PREFECTURE
,'大学院' AS KIND
,5 AS KIND_NUM
,SUM(e.GRADUATE) AS AMT 
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020
GROUP BY p.PF_NAME
)
WHERE AMT IS NOT NULL
ORDER BY
CODE, KIND_NUM
;