SELECT
    E.SURVEY_YEAR AS SV_YEAR,
    P.PF_NAME AS PREFECTURE,
    CASE
        WHEN E.SUMA THEN '小学校'
        WHEN E.SUMB THEN '中学校'
        WHEN E.SUMC THEN '高校'
        WHEN E.SUMD THEN '短大'
        WHEN E.SUME THEN '大学'
        WHEN E.SUMF THEN '大学院'
        END AS KIND,

    CASE
        WHEN SUM(ELEMENTARY) THEN SUM(ELEMENTARY)
        WHEN SUM(MIDDLE) THEN SUM(MIDDLE)
        WHEN SUM(HIGH) THEN SUM(HIGH)
        WHEN SUM(JUNIOR_CLG) THEN SUM(JUNIOR_CLG)
        WHEN SUM(COLLEGE) THEN SUM(COLLEGE)
        WHEN SUM(GRADUATE) THEN SUM(GRADUATE)
        END AS AMT
FROM
    (
    SELECT
        SURVEY_YEAR,
        PF_CODE,
        SUM(ELEMENTARY) AS SUMA,
        SUM(MIDDLE) AS SUMB,
        SUM(HIGH) AS SUMC,
        SUM(JUNIOR_CLG) AS SUMD,
        SUM(COLLEGE) AS SMUE,
        SUM(GRADUATE) AS SUMF
    FROM
        ENROLLMENT_STATUS
    WHERE 
        SURVEY_YEAR = 2020
    GROUP BY
        SURVEY_YEAR,
        PF_CODE
    ) AS E
INNER JOIN
    PREFECTURE AS P
ON
    E.PF_CODE = P.PF_CODE
GROUP BY
    E.SURVEY_YEAR,
    P.PF_NAME,
    E.KIND
ORDER BY
    PREFECTURE ASC;