WITH IMMI_SUMMARY AS
(
    select PORT_CODE, KIND_CODE, GRP.GROUP_CODE, SUM(AMT) as AMT from IMMIGRATION
    inner join GRP
    on GRP.GROUP_CODE = IMMIGRATION.GROUP_CODE
    WHERE KIND_CODE in ('110', '120')
    and GRP.GROUP_CODE in ('120')
    group by PORT_CODE, KIND_CODE, GRP.GROUP_CODE
)
select 
    PORT_CODE as 港コード
    ,PORT_NAME as 港名
    ,inpeople as '入国者数'
    ,outpeople as '出国者数'
    ,diff as '差分'
from (select
    PORT_CODE
    ,PORT_NAME
    ,IFNULL((select AMT from IMMI_SUMMARY where IMMI_SUMMARY.PORT_CODE = PORT.PORT_CODE and KIND_CODE = '110'), 0) as inpeople
    ,IFNULL((select AMT from IMMI_SUMMARY where IMMI_SUMMARY.PORT_CODE = PORT.PORT_CODE and KIND_CODE = '120'), 0) as outpeople
    ,IFNULL((select AMT from IMMI_SUMMARY where IMMI_SUMMARY.PORT_CODE = PORT.PORT_CODE and KIND_CODE = '110'), 0) 
     - IFNULL((select AMT from IMMI_SUMMARY where IMMI_SUMMARY.PORT_CODE = PORT.PORT_CODE and KIND_CODE = '120'), 0) as diff
from PORT)
where diff > 0
order by diff desc, PORT_CODE desc