select 
IMMIGRATION.PORT_CODE as '港コード',
PORT.PORT_NAME as '港名',
sum(IM.AMT) as '入国者数',
sum(IMMIGRATION.AMT) as '出国者数'
--diff(IMMIGRATION.AMT - IM.AMT) as '差分'


from IMMIGRATION
inner join PORT on
    PORT.PORT_CODE = IMMIGRATION.PORT_CODE

inner join GRP on
    GRP.GROUP_CODE = IMMIGRATION.GROUP_CODE
    --and IMMIGRATION.GROUP_CODE in ('120')
    
left join IMMIGRATION AS IM on
    IM.PORT_CODE = IMMIGRATION.PORT_CODE
    and IM.GROUP_CODE = IMMIGRATION.GROUP_CODE
    and IM.KIND_CODE = IMMIGRATION.KIND_CODE
    and IM.GROUP_CODE = '110'
--where IMMIGRATION.GROUP_CODE
    
/*
    select *
    from IMMIGRATION AS 110
    where IMMIGRATION.GROUP_CODE in ('110')
*/
group by IMMIGRATION.PORT_CODE
order by IMMIGRATION.PORT_CODE desc