with temp as(
    select  
        port_code  
        ,port_name  
        ,sum(  
            case  
                when kind_code = '110'  
                then amt  
                else 0  
            end   
            ) as in_amt  
        ,sum(  
            case  
                when kind_code = '120'  
                then amt  
                else 0  
            end   
            ) as out_amt  
    from  
    (  
        select  
        *  
        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  
    ) as subquery  
    group by   
        port_code, port_name  
)
select
    port_code as '港コード'
    ,port_name as '港名'
    ,in_amt as '入国者数'
    ,out_amt as '出国者数'
    ,in_amt - out_amt as '差分'
from 
    temp
where
    in_amt - out_amt > 0
order by 
    5 desc
    ,1 desc