with pre as
(
select
    p.PORT_CODE as 港コード
    ,max(p.PORT_NAME) as 港名
    ,sum(case when group_name = '外国人' and kind_code = '110' then amt else 0 end) as 入国者数
    ,sum(case when group_name = '外国人' and kind_code = '120' then amt else 0 end) as 出国者数
from
    immigration i
    left join
    port p
        on
        i.port_code = p.port_code
    left join
    grp g
        on
        i.group_code = g.group_code
group by
    p.PORT_CODE
)
select
    港コード
    ,港名
    ,入国者数
    ,出国者数
    ,入国者数 - 出国者数 as 差分
from
    pre
order by
    入国者数 - 出国者数 desc
    ,港コード desc