--survey_YEAR->SV_year=2020
--PF_NAME->PREFECTURE
--都道府県、就学先ごとで集計
--都道府県でGroupby->ElementaryのsumをAMTに入れる？
with base as (
SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    '小学校' AS KIND,
    'a' AS ord,
    SUM(ELEMENTARY) AS AMT
FROM
    ENROLLMENT_STATUS
WHERE
    SURVEY_YEAR = 2020 and ELEMENTARY is not null
GROUP BY
    PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    '中学校' AS KIND,
    'b' AS ord,
    SUM(MIDDLE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and MIDDLE is not null
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    '高校' AS KIND,
    'c' AS ord,
    SUM(HIGH) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and HIGH is not null
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    '短大' AS KIND,
    'd' AS ord,
    SUM(JUNIOR_CLG) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and JUNIOR_CLG is not null
    GROUP BY
        PF_CODE
union
    SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    '大学' AS KIND,
    'e' AS ord,
    SUM(COLLEGE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and COLLEGE is not null
    GROUP BY
        PF_CODE
union
     SELECT
    SURVEY_YEAR AS SV_YEAR,
    PF_CODE ,
    '大学院' AS KIND,
    'f' AS ord,
    SUM(GRADUATE) AS AMT
    FROM
        ENROLLMENT_STATUS
    WHERE   
        SURVEY_YEAR = 2020 and GRADUATE is not null
    GROUP BY
        PF_CODE
)
select
    SV_YEAR,
    PF_NAME AS PREFECTURE,
    KIND,
    AMT
from 
    base
inner join
    PREFECTURE p
on  base.PF_CODE = p.PF_CODE
order by base.PF_CODE ASC, base.ord ASC