with tmp as (
  select
    i.port_code
    , p.port_name
    , case
        when i.kind_code = '110' then i.amt
        else 0
      end as `in_p`
    , case
        when i.kind_code = '120' then i.amt
        else 0
      end as `out_p`
  from
    immigration as i
  left join
    port as p
    using (port_code)
  left join
    grp as g
    using (group_code)
  where
    g.group_name = '外国人'

)

select
  port_code as `港コード`
  , port_name as `港名`
  , in_p as `入国者数`
  , out_p as `出国者数`
  , in_p - out_p as `差分`
from (
  select
    port_code
    , port_name
    , sum(in_p) as `in_p`
    , sum(out_p) as `out_p`
  from tmp
  group by port_code, port_name

)
where `差分` > 0
order by `差分` desc, port_code desc