-- DISTRICT_CODE as '地区コード'
-- DISTRICT_NAME as '地区名'
-- TOTAL_AMT as '総人口'

with IN_T as
         (select PORT_CODE,
                 SUM(AMT) as AMT
          from IMMIGRATION
          where KIND_CODE = '110'
          group by PORT_CODE),
     OUT_T as
         (select PORT_CODE,
                 SUM(AMT) as AMT
          from IMMIGRATION
          where KIND_CODE = '120'
          group by PORT_CODE)
select PORT.PORT_CODE         as '港コード',
       PORT.PORT_NAME         as '港名',
       COALESCE(IN_T.AMT,0)               as '入国者数',
       coalesce( OUT_T.AMT,0)              as '出国者数',
       (COALESCE(IN_T.AMT,0)-coalesce( OUT_T.AMT,0)) as '差分'
FROM PORT
         left join IN_T on IN_T.PORT_CODE = PORT.PORT_CODE
         left join OUT_T on PORT.PORT_CODE = OUT_T.PORT_CODE

order by 5 desc, 1 desc;