SELECT 
    PF.PF_CODE AS 都道府県コード,
    PF.PF_NAME AS 都道府県名,
	CASE WHEN FO.rank_result = 1 THEN NA.NATION_NAME ELSE "" END AS "1位 国名",
	SUM(CASE WHEN FO.rank_result = 1 THEN FO.NATION_NAME ELSE 0 END) AS "1位 国名",
	CASE WHEN FO.rank_result = 2 THEN NA.NATION_NAME ELSE "" END AS "2位 国名",
	SUM(CASE WHEN FO.rank_result = 2 THEN FO.NATION_NAME ELSE 0 END) AS "2位 国名",
	CASE WHEN FO.rank_result = 3 THEN NA.NATION_NAME ELSE "" END AS "3位 国名",
	SUM(CASE WHEN FO.rank_result = 3 THEN FO.NATION_NAME ELSE 0 END) AS "3位 国名",
    AGE_GRP.AGE_NAME AS 合計人数
FROM PREFECTURE AS PF
     INNER JOIN (SELECT PF_CODE, NATION_CODE, AMT, 
     			RANK() OVER(PARTITION BY PF_CODE ORDER BY AMT DESC) AS rank_result 
     			FROM FOREIGNER WHERE NATION_CODE <> 113) AS FO
         ON PF.PF_CODE = FO.PF_CODE
	INNER JOIN NATIONALITY AS NA 
		 ON FO.NATION_CODE = NA.NATION_CODE
ORDER BY 合計人数 DESC, PF_CODE;