SELECT
PORT_CODE AS '港コード',
PORT_NAME AS '港名',
ENTRY AS '入国者数',
EXIT AS '出国者数',
ENTRY - EXIT AS '差分'
FROM
(SELECT
t1.PORT_CODE,
PORT_NAME,
SUM(CASE
WHEN KIND_CODE = '110'
THEN AMT
ELSE 0
END) AS ENTRY,
SUM(
CASE
WHEN KIND_CODE = '120'
THEN AMT
ELSE 0
END) AS EXIT
FROM
IMMIGRATION t1
LEFT JOIN
PORT t2
ON
t1.PORT_CODE = t2.PORT_CODE
WHERE
GROUP_CODE = '120'
GROUP BY
t1.PORT_CODE)
WHERE ENTRY - EXIT > 0
ORDER BY
ENTRY - EXIT DESC,
PORT_CODE DESC