-- https://topsic-contest.jp/contests/contest002/problems/contest002-4
with base as (
              SELECT SURVEY_YEAR, PF_CODE, '0' AS KIND_PRI, '小学校' AS KIND, SUM(case when ELEMENTARY is null then 0 else ELEMENTARY end ) 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(case when MIDDLE is null then 0 else MIDDLE end )     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(case when HIGH is null then 0 else HIGH end )       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(case when JUNIOR_CLG is null then 0 else JUNIOR_CLG end ) 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(case when COLLEGE is null then 0 else COLLEGE end )    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(case when GRADUATE is null then 0 else GRADUATE end )   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 and base.AM
    base.AMT > 0
ORDER BY 
    base.PF_CODE ASC, base.KIND_PRI ASC