select
	f.*
	,sub.AMT as 合計人数
from
	(
		select
			 f.PF_CODE as 都道府県コード
			,p.PF_NAME as 都道府県名
			,max(case when f.num = 1 then n.NATION_NAME end) as "1位 国名"
			,max(case when f.num = 1 then f.AMT end) as "1位 人数"
			,max(case when f.num = 2 then n.NATION_NAME end) as "2位 国名"
			,max(case when f.num = 2 then f.AMT end) as "2位 人数"
			,max(case when f.num = 3 then n.NATION_NAME end) as "3位 国名"
			,max(case when f.num = 3 then f.AMT end) as "3位 人数"
		from
			(
				select
					f.PF_CODE
					,f.NATION_CODE
					,f.AMT
					,row_number() over (partition by f.PF_CODE order by f.AMT desc) as num
				from
					(
						select
							PF_CODE
							,NATION_CODE
							,SUM(AMT) as AMT
						from
							FOREIGNER
						where
							NATION_CODE <> '113'
						group by
							PF_CODE
							,NATION_CODE
					) as f
			) f
			inner join NATIONALITY as n
				on f.NATION_CODE = n.NATION_CODE
			inner join PREFECTURE as p
				on f.PF_CODE = p.PF_CODE
		where
			f.num <= 3
		group by
			 f.PF_CODE
			,p.PF_NAME
	) f
	inner join (
		select
			PF_CODE
			,SUM(AMT) as AMT
		from
			FOREIGNER
		where
			NATION_CODE <> '113'
		GROUP BY
			PF_CODE
	) as sub
		on f.都道府県コード = sub.PF_CODE
order by
	9 desc, 1