SELECT
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
FROM 
(SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '小学校' AS KIND,
    SUM(ELEMENTARY) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
LEFT JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE ELEMENTARY IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
UNION ALL
SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '中学校' AS KIND,
    SUM(MIDDLE) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
LEFT JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE MIDDLE IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
    
UNION ALL
SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '高校' AS KIND,
    SUM(HIGH) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
LEFT JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE HIGH IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
UNION ALL
SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '短大' AS KIND,
    SUM(JUNIOR_CLG) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
LEFT JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE JUNIOR_CLG IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
    
UNION ALL
SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '大学' AS KIND,
    SUM(COLLEGE) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
LEFT JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE COLLEGE IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
    
UNION ALL
SELECT
    PF.PF_CODE AS PF_CODE,
    SURVEY_YEAR AS SV_YEAR,
    PF_NAME AS PREFECTURE,
    '大学院' AS KIND,
    SUM(GRADUATE) AS AMT
FROM
    ENROLLMENT_STATUS AS EN
LEFT JOIN
    PREFECTURE AS PF
ON
    PF.PF_CODE = EN.PF_CODE
WHERE PF.PF_CODE NOT IN (
    SELECT DISTINCT PF_CODE
    FROM ENROLLMENT_STATUS
    WHERE GRADUATE IS NULL
)
AND SURVEY_YEAR = 2020
GROUP BY
    EN.PF_CODE
)
ORDER BY
    PF_CODE,
    CASE KIND
        WHEN '小学校' THEN 0
        WHEN '中学校' THEN 1
        WHEN '高校' THEN 2
        WHEN '短大' THEN 3
        WHEN '大学' THEN 4
        WHEN '大学院' THEN 5
        ELSE 6
    END