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