CREATE TEMPORARY TABLE RESULT AS
    SELECT
        SURVEY_YEAR
        ,PREFECTURE.PF_CODE
        ,PF_NAME
        , "小学校" AS "KIND"
        ,SUM(ELEMENTARY) AS AMT
    FROM
        ENROLLMENT_STATUS
    LEFT JOIN PREFECTURE ON PREFECTURE.PF_CODE = ENROLLMENT_STATUS.PF_CODE
    WHERE
        ELEMENTARY IS NOT NULL AND SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, ENROLLMENT_STATUS.PF_CODE

    UNION ALL

    SELECT
        SURVEY_YEAR
        ,PREFECTURE.PF_CODE
        ,PF_NAME
        , "中学校" AS "KIND"
        ,SUM(MIDDLE) AS AMT
    FROM
        ENROLLMENT_STATUS
    LEFT JOIN PREFECTURE ON PREFECTURE.PF_CODE = ENROLLMENT_STATUS.PF_CODE
    WHERE
        MIDDLE IS NOT NULL AND SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, ENROLLMENT_STATUS.PF_CODE

    UNION ALL

    SELECT
        SURVEY_YEAR
        ,PREFECTURE.PF_CODE
        ,PF_NAME
        , "高校" AS "KIND"
        ,SUM(HIGH) AS AMT
    FROM
        ENROLLMENT_STATUS
    LEFT JOIN PREFECTURE ON PREFECTURE.PF_CODE = ENROLLMENT_STATUS.PF_CODE
    WHERE
        HIGH IS NOT NULL AND SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, ENROLLMENT_STATUS.PF_CODE

    UNION ALL

    SELECT
        SURVEY_YEAR
        ,PREFECTURE.PF_CODE
        ,PF_NAME
        , "短大" AS "KIND"
        ,SUM(JUNIOR_CLG) AS AMT
    FROM
        ENROLLMENT_STATUS
    LEFT JOIN PREFECTURE ON PREFECTURE.PF_CODE = ENROLLMENT_STATUS.PF_CODE
    WHERE
        JUNIOR_CLG IS NOT NULL AND SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, ENROLLMENT_STATUS.PF_CODE

    UNION ALL

    SELECT
        SURVEY_YEAR
        ,PREFECTURE.PF_CODE
        ,PF_NAME
        , "大学" AS "KIND"
        ,SUM(COLLEGE) AS AMT
    FROM
        ENROLLMENT_STATUS
    LEFT JOIN PREFECTURE ON PREFECTURE.PF_CODE = ENROLLMENT_STATUS.PF_CODE
    WHERE
        COLLEGE IS NOT NULL AND SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, ENROLLMENT_STATUS.PF_CODE

    UNION ALL

    SELECT
        SURVEY_YEAR
        ,PREFECTURE.PF_CODE
        ,PF_NAME
        , "大学院" AS "KIND"
        ,SUM(GRADUATE) AS AMT
    FROM
        ENROLLMENT_STATUS
    LEFT JOIN PREFECTURE ON PREFECTURE.PF_CODE = ENROLLMENT_STATUS.PF_CODE
    WHERE
        GRADUATE IS NOT NULL AND SURVEY_YEAR = 2020
    GROUP BY SURVEY_YEAR, ENROLLMENT_STATUS.PF_CODE
;

SELECT
    SURVEY_YEAR AS SV_YEAR
    ,PF_NAME AS PREFECTURE
    ,KIND
    ,AMT
FROM
    RESULT
ORDER BY PF_CODE