select
	t3.PORT_CODE as `港コード`,
	t3.PORT_NAME as `港名`,
	AMT_Immigration as `入国者数`,
	AMT_departure as `出国者数`,
	AMT_Immigration - AMT_departure as `差分`
from
	(
		select
			PORT_CODE,
			GROUP_CODE,
			sum(case when KIND_CODE = '110' then AMT else NULL end) as AMT_Immigration,
			sum(case when KIND_CODE = '120' then AMT else NULL end) as AMT_departure
		from
			IMMIGRATION
		group by 1,2
	) t1
inner join
	GRP t2
	on t1.GROUP_CODE = t2.GROUP_CODE
inner join
	PORT t3
	on t1.PORT_CODE = t3.PORT_CODE
where
	t2.GROUP_NAME = '外国人'
and t1.AMT_Immigration > t1.AMT_departure
order by 5 desc, 1 desc