select
    temp.port_code 港コード,
    temp.port_name 港名,
    temp.in_p 入国者数,
    temp.out_p 出国者数,
    (temp.in_p - temp.out_p) 差分
from
(
    select
        p.port_code,
        p.port_name,
        sum(case when i.kind_code = '110' then i.amt else 0 end) in_p,
        sum(case when i.kind_code = '120' then i.amt else 0 end) out_p
    from immigration i
    join port p on i.port_code = p.port_code
    join grp g on i.group_code = g.group_code
    where 1 = 1
    and g.group_code = '120'
    group by p.port_code, p.port_name
)temp
where 1 = 1
and temp.in_p > temp.out_p
order by 5 desc, 1 desc
;