-- ２０２０年のデータのみ取り出す

WITH tbl_2020 AS (
    SELECT
        *
    FROM
        ENROLLMENT_STATUS
    WHERE
        SURVEY_YEAR = 2020
        ),
tbl1 AS (
    SELECT
        PF_CODE,
        SUM(ELEMENTARY) AS AMT,
        1 AS KIND
    FROM
        tbl_2020
    WHERE 
        ELEMENTARY IS NOT NULL
    GROUP BY
        PF_CODE
),
tbl2 AS (
    SELECT
        PF_CODE,
        SUM(MIDDLE) AS AMT,
        2 AS KIND
    FROM
        tbl_2020
    WHERE 
        MIDDLE IS NOT NULL
    GROUP BY
        PF_CODE
),
tbl3 AS (
    SELECT
        PF_CODE,
        SUM(HIGH) AS AMT,
        3 AS KIND
    FROM
        tbl_2020
    WHERE 
        HIGH IS NOT NULL
    GROUP BY
        PF_CODE
),
tbl4 AS (
    SELECT
        PF_CODE,
        SUM(JUNIOR_CLG) AS AMT,
        4 AS KIND
    FROM
        tbl_2020
    WHERE 
        JUNIOR_CLG IS NOT NULL
    GROUP BY
        PF_CODE
),
tbl5 AS (
    SELECT
        PF_CODE,
        SUM(COLLEGE) AS AMT,
        5 AS KIND
    FROM
        tbl_2020
    WHERE 
        COLLEGE IS NOT NULL
    GROUP BY
        PF_CODE
),
tbl6 AS (
    SELECT
        PF_CODE,
        SUM(GRADUATE) AS AMT,
        6 AS KIND
    FROM
        tbl_2020
    WHERE 
        GRADUATE IS NOT NULL
    GROUP BY
        PF_CODE
),
tbl_all AS (
SELECT * FROM tbl1
UNION ALL
SELECT * FROM tbl2
UNION ALL
SELECT * FROM tbl3
UNION ALL
SELECT * FROM tbl4
UNION ALL
SELECT * FROM tbl5
UNION ALL
SELECT * FROM tbl6
)

SELECT
    2020 AS  SV_YEAR,
    PREFECTURE.PF_NAME AS PREFECTURE,
    CASE
        WHEN tbl_all.KIND = 1 THEN '小学校'
        WHEN tbl_all.KIND = 2 THEN '中学校'
        WHEN tbl_all.KIND = 3 THEN '高校'
        WHEN tbl_all.KIND = 4 THEN '短大'
        WHEN tbl_all.KIND = 5 THEN '大学'
        WHEN tbl_all.KIND = 6 THEN '大学院'
        ELSE 'hoge' END AS KIND,
    tbl_all.AMT
FROM tbl_all
LEFT JOIN
    PREFECTURE
    ON tbl_all.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
    tbl_all.PF_CODE, tbl_all.KIND

-- narabekae and namekae


-- SELECT 
--     SURVEY_YEAR AS SV_YEAR
--     PF_NAME AS PREFECTURE
--     集計した就学先の種類 AS KIND
--     SUM() AS AMT
-- FROM
--     ENROLLMENT_STATUS