select
    imi.port_code as 港コード	,
    max(port.port_name) as 港名	,
    sum(
        case
            when imi.kind_code = 110 then imi.amt
            else 0
        end
    ) as 入国者数	,
    sum(
        case
            when imi.kind_code = 120 then imi.amt
            else 0
        end
    ) as 出国者数	,
    sum(
        case
            when imi.kind_code = 110 then imi.amt
            else 0
        end
    ) - sum(
        case
            when imi.kind_code = 120 then imi.amt
            else 0
        end
    ) 差分
from
    IMMIGRATION imi
    left join
        grp gp
    on  imi.group_code = gp.group_code
    left join port
    on imi.PORT_CODE = port.PORT_CODE
where 
1=1
-- and imi.port_code = 51270
and gp.GROUP_CODE = 120
group by
    imi.port_code
having sum(
    case
        when imi.kind_code = 110 then imi.amt
        else 0
    end
) - sum(
    case
        when imi.kind_code = 120 then imi.amt
        else 0
    end
) > 0
order by sum(
        case
            when imi.kind_code = 110 then imi.amt
            else 0
        end
    ) - sum(
        case
            when imi.kind_code = 120 then imi.amt
            else 0
        end
    ) desc,
    imi.port_code desc
;