-- https://topsic-contest.jp/contests/contest002/problems/contest002-4
with base as (
              SELECT SURVEY_YEAR, PF_CODE, '0' AS KIND_PRI, '小学校' AS KIND, SUM(ELEMENTARY) AS AMT FROM ENROLLMENT_STATUS WHERE SURVEY_YEAR = 2020 GROUP BY SURVEY_YEAR, PF_CODE
    UNION ALL SELECT SURVEY_YEAR, PF_CODE, '1' AS KIND_PRI, '中学校' AS KIND, SUM(MIDDLE)     AS AMT FROM ENROLLMENT_STATUS WHERE SURVEY_YEAR = 2020 GROUP BY SURVEY_YEAR, PF_CODE
    UNION ALL SELECT SURVEY_YEAR, PF_CODE, '2' AS KIND_PRI, '高校'   AS KIND, SUM(HIGH)       AS AMT FROM ENROLLMENT_STATUS WHERE SURVEY_YEAR = 2020 GROUP BY SURVEY_YEAR, PF_CODE
    UNION ALL SELECT SURVEY_YEAR, PF_CODE, '3' AS KIND_PRI, '短大'   AS KIND, SUM(JUNIOR_CLG) AS AMT FROM ENROLLMENT_STATUS WHERE SURVEY_YEAR = 2020 GROUP BY SURVEY_YEAR, PF_CODE
    UNION ALL SELECT SURVEY_YEAR, PF_CODE, '4' AS KIND_PRI, '大学'   AS KIND, SUM(COLLEGE)    AS AMT FROM ENROLLMENT_STATUS WHERE SURVEY_YEAR = 2020 GROUP BY SURVEY_YEAR, PF_CODE
    UNION ALL SELECT SURVEY_YEAR, PF_CODE, '5' AS KIND_PRI, '大学院' AS KIND, SUM(GRADUATE)   AS AMT FROM ENROLLMENT_STATUS WHERE SURVEY_YEAR = 2020 GROUP BY SURVEY_YEAR, PF_CODE
)
SELECT
    base.SURVEY_YEAR AS SV_YEAR,
    PR.PF_NAME     AS PREFECTURE,
    base.KIND        AS KIND,
    base.AMT         AS AMT
FROM base
INNER JOIN 
    PREFECTURE PR
ON base.PF_CODE = PR.PF_CODE
WHERE 
    base.AMT IS NOT NULL
    --base.AMT > 0
ORDER BY 
    base.PF_CODE ASC, base.KIND_PRI ASC