with immigrations as (
    select
        P.port_code,
        sum(case when I.kind_code = '110' then I.amt else 0 end) as in_count,
        sum(case when I.kind_code = '120' then I.amt else 0 end) as out_count
    from
        port P
        inner join immigration I
        on P.port_code = I.port_code
    where
        I.group_code = '120'
    group by
        P.port_code
)
select
    P.port_code as 港コード,
    P.port_name as 港名,
    I.in_count as 入国者数,
    I.out_count as 出国者数,
    I.in_count - I.out_count as 差分
from
    immigrations I
    inner join port P
    on I.port_code = P.port_code
where
    I.in_count > I.out_count
order by
    I.in_count - I.out_count desc, P.port_code desc
;