select distinct
pf.pf_code as 都道府県コード
,pf.pf_name as 都道府県名
,amt.amtf as 合計人数
from foreigner as f
left outer  join prefecture as pf
on f.pf_code = pf.pf_code
left outer join(select pf_code,sum(amt) as amtf from foreigner where nation_code not in ('113') group by pf_code) as amt
on amt.pf_code = f.pf_code
order by 合計人数 desc,都道府県コード asc