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
,COUNT(*) AS CNT
,COUNT(e.ELEMENTARY) AS KIND_CNT
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 
,COUNT(*) AS CNT
,COUNT(e.MIDDLE) AS KIND_CNT
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_NUM
,2 AS NUM_NUM
,SUM(e.HIGH) AS AMT 
,COUNT(*) AS CNT
,COUNT(e.HIGH) AS KIND_CNT
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020 AND e.HIGH IS NOT NULL
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_NUM
,3 AS NUM_NUM
,SUM(e.JUNIOR_CLG) AS AMT 
,COUNT(*) AS CNT
,COUNT(e.JUNIOR_CLG) AS KIND_CNT
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020 AND e.JUNIOR_CLG IS NOT NULL
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_NUM
,4 AS NUM_NUM
,SUM(e.COLLEGE) AS AMT 
,COUNT(*) AS CNT
,COUNT(e.COLLEGE) AS KIND_CNT
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020 AND e.COLLEGE IS NOT NULL
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_NUM
,5 AS NUM_NUM
,SUM(e.GRADUATE) AS AMT 
,COUNT(*) AS CNT
,COUNT(e.GRADUATE) AS KIND_CNT
FROM 
ENROLLMENT_STATUS AS e
INNER JOIN PREFECTURE AS p
WHERE e.SURVEY_YEAR = 2020 AND e.GRADUATE IS NOT NULL
GROUP BY p.PF_NAME
)
WHERE
CNT = KIND_CNT
ORDER BY
CODE, KIND_NUM
;