SELECT
    MainData.SURVEY_YEAR
    ,MainData.PF_NAME
    ,MainData.KIND
    ,SUM(AMT) AMT
FROM
    (
        SELECT
        	ENROLLMENT_STATUS.SURVEY_YEAR
        	,PREFECTURE.PF_CODE
        	,PREFECTURE.PF_NAME
        	,'小学生' as KIND
        	,ELEMENTARY as AMT
            ,0 Div
        FROM
        	ENROLLMENT_STATUS
        	    LEFT JOIN PREFECTURE
        			ON	ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
        WHERE
            ELEMENTARY <> 0
        UNION ALL
        SELECT
        	ENROLLMENT_STATUS.SURVEY_YEAR
        	,PREFECTURE.PF_CODE
        	,PREFECTURE.PF_NAME
        	,'中学生' as KIND
        	,MIDDLE as AMT
            ,1 Div
        FROM
        	ENROLLMENT_STATUS
        	    LEFT JOIN PREFECTURE
        			ON	ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
        WHERE
            MIDDLE <> 0
        UNION ALL
        SELECT
        	ENROLLMENT_STATUS.SURVEY_YEAR
        	,PREFECTURE.PF_CODE
        	,PREFECTURE.PF_NAME
        	,'高校生' as KIND
        	,HIGH as AMT
            ,2 Div
        FROM
        	ENROLLMENT_STATUS
        	    LEFT JOIN PREFECTURE
        			ON	ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
        WHERE
            HIGH <> 0
        UNION ALL
        SELECT
        	ENROLLMENT_STATUS.SURVEY_YEAR
        	,PREFECTURE.PF_CODE
        	,PREFECTURE.PF_NAME
        	,'短期大学' as KIND
        	,JUNIOR_CLG as AMT
            ,3 Div
        FROM
        	ENROLLMENT_STATUS
        	    LEFT JOIN PREFECTURE
        			ON	ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
        WHERE
            JUNIOR_CLG <> 0
        UNION ALL
        SELECT
        	ENROLLMENT_STATUS.SURVEY_YEAR
        	,PREFECTURE.PF_CODE
        	,PREFECTURE.PF_NAME
        	,'大学' as KIND
        	,COLLEGE as AMT
            ,4 Div
        FROM
        	ENROLLMENT_STATUS
        	    LEFT JOIN PREFECTURE
        			ON	ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
        WHERE
            COLLEGE <> 0
        UNION ALL
        SELECT
        	ENROLLMENT_STATUS.SURVEY_YEAR
        	,PREFECTURE.PF_CODE
        	,PREFECTURE.PF_NAME
        	,'大学院' as KIND
        	,GRADUATE as AMT
            ,5 Div
        FROM
        	ENROLLMENT_STATUS
        	    LEFT JOIN PREFECTURE
        			ON	ENROLLMENT_STATUS.PF_CODE = PREFECTURE.PF_CODE
        WHERE
            GRADUATE <> 0
    ) MainData
WHERE
    MainData.SURVEY_YEAR = 2020
AND AMT IS NOT NULL
GROUP BY
    MainData.SURVEY_YEAR
    ,MainData.PF_NAME
    ,MainData.KIND
ORDER BY 
    MainData.PF_CODE
    ,MainData.Div