WITH
ELEMENTARY AS
(
    SELECT
        SURVEY_YEAR
    ,   PF_CODE
    ,   '小学校' AS KIND
    ,   SUM(ELEMENTARY) AS AMT
    ,   1 AS ORDER_KEY
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR
    ,   PF_CODE
),
MIDDLE AS
(
    SELECT
        SURVEY_YEAR
    ,   PF_CODE
    ,   '中学校' AS KIND
    ,   SUM(MIDDLE) AS AMT
    ,   2 AS ORDER_KEY
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR
    ,   PF_CODE
),
HIGH AS
(
    SELECT
        SURVEY_YEAR
    ,   PF_CODE
    ,   '高校' AS KIND
    ,   SUM(HIGH) AS AMT
    ,   3 AS ORDER_KEY
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR
    ,   PF_CODE
),
JUNIOR_CLG AS
(
    SELECT
        SURVEY_YEAR
    ,   PF_CODE
    ,   '短大' AS KIND
    ,   SUM(JUNIOR_CLG) AS AMT
    ,   4 AS ORDER_KEY
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR
    ,   PF_CODE
),
COLLEGE AS
(
    SELECT
        SURVEY_YEAR
    ,   PF_CODE
    ,   '大学' AS KIND
    ,   SUM(COLLEGE) AS AMT
    ,   5 AS ORDER_KEY
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR
    ,   PF_CODE
),
GRADUATE AS
(
    SELECT
        SURVEY_YEAR
    ,   PF_CODE
    ,   '大学院' AS KIND
    ,   SUM(GRADUATE) AS AMT
    ,   5 AS ORDER_KEY
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR
    ,   PF_CODE
),
TOTAL AS
(
    SELECT * FROM ELEMENTARY
    UNION ALL
    SELECT * FROM MIDDLE
    UNION ALL
    SELECT * FROM HIGH
    UNION ALL
    SELECT * FROM JUNIOR_CLG
    UNION ALL
    SELECT * FROM COLLEGE
    UNION ALL
    SELECT * FROM GRADUATE
)
SELECT 
    TOTAL.SURVEY_YEAR  AS SV_YEAR
,   P.PF_NAME               AS PREFECTURE
,   TOTAL.KIND AS KIND
,   IFNULL(TOTAL.AMT, 0) AS AMT
FROM
    PREFECTURE P
INNER JOIN 
    TOTAL
    ON P.PF_CODE = TOTAL.PF_CODE
ORDER BY
    P.PF_CODE ASC
,   TOTAL.ORDER_KEY ASC;