with IMM as (
select  
 PORT_CODE
,GROUP_CODE
,sum(case when KIND_CODE = '110' then AMT end) INAMT
,sum(case when KIND_CODE = '120' then AMT end) OUTAMT
from [IMMIGRATION]
group by  PORT_CODE, GROUP_CODE
)


select 

 i.PORT_CODE as 港コード
,p.PORT_NAME as 港名
,i.INAMT as 入国者数
,i.OUTAMT as 出国者数
,i.INAMT - i.OUTAMT as 差分
 
from IMM  i
left join PORT p on p.PORT_CODE = i.PORT_CODE
left join GRP g on g.GROUP_CODE = i.GROUP_CODE

where i.GROUP_CODE = '120'
and   i.INAMT > i.OUTAMT

order by 差分 desc ,  i.PORT_CODE desc