SELECT
PORT_CODE AS "港コード"
, PORT_NAME AS "港名"
, in_amt AS "入国者数"
, out_amt AS "出国者数"
, in_amt - out_amt AS "差分"
 
FROM
(
SELECT
i.PORT_CODE
, p.PORT_NAME
, SUM(CASE WHEN i.KIND_CODE = '110' THEN i.AMT ELSE 0 END) as in_amt
, SUM(CASE WHEN i.KIND_CODE = '120' THEN i.AMT ELSE 0 END) as out_amt

FROM
IMMIGRATION as i

LEFT OUTER JOIN
PORT as p
on
i.PORT_CODE = p.PORT_CODE

WHERE
i.GROUP_CODE = '120'

GROUP BY
i.PORT_CODE
, p.PORT_NAME
)
WHERE
(in_amt - out_amt) > 0

ORDER BY 
5 DESC
, 1 DESC