SELECT
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
FROM( 
    SELECT
        PF_CODE,
        SURVEY_YEAR AS SV_YEAR,
        PF_NAME AS PREFECTURE,
        '小学校' AS KIND,
        SUM(ELEMENTARY) AS AMT
    FROM
        ENROLLMENT_STATUS AS ES
        INNER JOIN PREFECTURE AS P USING(PF_CODE)
    WHERE
        SV_YEAR = 2020
    GROUP BY
        ES.PF_CODE
UNION ALL
    SELECT
        PF_CODE,
        SURVEY_YEAR AS SV_YEAR,
        PF_NAME AS PREFECTURE,
        '中学校' AS KIND,
        SUM(MIDDLE) AS AMT
    FROM
        ENROLLMENT_STATUS AS ES
        INNER JOIN PREFECTURE AS P USING(PF_CODE)
    WHERE
        SV_YEAR = 2020
    GROUP BY
        ES.PF_CODE
UNION ALL
    SELECT
        PF_CODE,
        SURVEY_YEAR AS SV_YEAR,
        PF_NAME AS PREFECTURE,
        '高校' AS KIND,
        SUM(HIGH) AS AMT
    FROM
        ENROLLMENT_STATUS AS ES
        INNER JOIN PREFECTURE AS P USING(PF_CODE)
    WHERE
        SV_YEAR = 2020
    GROUP BY
        ES.PF_CODE
UNION ALL
    SELECT
        PF_CODE,
        SURVEY_YEAR AS SV_YEAR,
        PF_NAME AS PREFECTURE,
        '短大' AS KIND,
        SUM(JUNIOR_CLG) AS AMT
    FROM
        ENROLLMENT_STATUS AS ES
        INNER JOIN PREFECTURE AS P USING(PF_CODE)
    WHERE
        SV_YEAR = 2020
    GROUP BY
        ES.PF_CODE
UNION ALL
    SELECT
        PF_CODE,
        SURVEY_YEAR AS SV_YEAR,
        PF_NAME AS PREFECTURE,
        '大学' AS KIND,
        SUM(COLLEGE) AS AMT
    FROM
        ENROLLMENT_STATUS AS ES
        INNER JOIN PREFECTURE AS P USING(PF_CODE)
    WHERE
        SV_YEAR = 2020
    GROUP BY
        ES.PF_CODE
UNION ALL
    SELECT
        PF_CODE,
        SURVEY_YEAR AS SV_YEAR,
        PF_NAME AS PREFECTURE,
        '大学院' AS KIND,
        SUM(GRADUATE) AS AMT
    FROM
        ENROLLMENT_STATUS AS ES
        INNER JOIN PREFECTURE AS P USING(PF_CODE)
    WHERE
        SV_YEAR = 2020
    GROUP BY
        ES.PF_CODE
)
WHERE AMT IS NOT NULL
ORDER BY
    PF_CODE ASC,
    CASE KIND
        WHEN '小学校' THEN 1
        WHEN '中学校' THEN 2
        WHEN '高校' THEN 3
        WHEN '短大' THEN 4
        WHEN '大学' THEN 5
        WHEN '大学院' THEN 6
	END;