SELECT
I.PORT_CODE AS '港コード',
P.PORT_NAME AS '港名',
SUM(CASE WHEN I.KIND_CODE = '110' THEN I.AMT ELSE 0 END) AS '入国者数',
SUM(CASE WHEN I.KIND_CODE = '120' THEN I.AMT ELSE 0 END) AS '出国者数',
SUM(CASE WHEN I.KIND_CODE = '110' THEN I.AMT ELSE 0 END) - SUM(CASE WHEN I.KIND_CODE = '120' THEN I.AMT ELSE 0 END) AS '差分'
FROM IMMIGRATION I
INNER JOIN PORT P
ON P.PORT_CODE = I.PORT_CODE
INNER JOIN GRP G
ON G.GROUP_CODE = I.GROUP_CODE
GROUP BY I.PORT_CODE
HAVING SUM(CASE WHEN I.KIND_CODE = '110' THEN I.AMT ELSE 0 END) - SUM(CASE WHEN I.KIND_CODE = '120' THEN I.AMT ELSE 0 END) > 0
ORDER BY SUM(CASE WHEN I.KIND_CODE = '110' THEN I.AMT ELSE 0 END) - SUM(CASE WHEN I.KIND_CODE = '120' THEN I.AMT ELSE 0 END) DESC, I.PORT_CODE DESC;