SELECT 
	 A.Survey_Year AS SV_YEAR
	,P.PF_NAME AS PREFECTURE
	,A.KIND AS KIND
	,A.AMT AS AMT
FROM 
	Prefecture P
INNER JOIN
(
	SELECT 
		Survey_Year 
		,PF_CODE 
		,'小学校' AS KIND
		,SUM(Elementary) AS AMT
	FROM 
		Enrollment_Status
	WHERE
		Survey_Year = 2020
	GROUP BY PF_CODE
	UNION ALL
	SELECT 
		Survey_Year 
		,PF_CODE 
		,'中学校' AS KIND
		,SUM(Middle) AS AMT
	FROM 
		Enrollment_Status
	WHERE
		Survey_Year = 2020
	GROUP BY PF_CODE
	UNION ALL
	SELECT 
		Survey_Year 
		,PF_CODE 
		,'高校' AS KIND
		,SUM(High) AS AMT
	FROM 
		Enrollment_Status
	WHERE
		Survey_Year = 2020
	GROUP BY PF_CODE
	UNION ALL
	SELECT 
		Survey_Year 
		,PF_CODE 
		,'短大' AS KIND
		,SUM(Junior_Clg) AS AMT
	FROM 
		Enrollment_Status
	WHERE
		Survey_Year = 2020
	GROUP BY PF_CODE
	UNION ALL
	SELECT 
		Survey_Year 
		,PF_CODE 
		,'大学' AS KIND
		,SUM(College) AS AMT
	FROM 
		Enrollment_Status
	WHERE
		Survey_Year = 2020
	GROUP BY PF_CODE
	UNION ALL
	SELECT 
		Survey_Year 
		,PF_CODE 
		,'大学院' AS KIND
		,SUM(Graduate) AS AMT
	FROM 
		Enrollment_Status
	WHERE
		Survey_Year = 2020
	GROUP BY PF_CODE
) A
ON 	
	P.PF_CODE = A.PF_CODE
WHERE
	A.AMT IS NOT NULL
ORDER BY
	P.PF_CODE ASC
	,CASE KIND
		WHEN '小学校' THEN 1
		WHEN '中学校' THEN 2
		WHEN '高校' THEN 3
		WHEN '短大' THEN 4
		WHEN '大学' THEN 5
		WHEN '大学院' THEN 6
	 END
;