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
where GRP.GROUP_CODE = '120' --外国人
group by IMM.PORT_CODE
having 入国者数>出国者数
order by 差分 desc,港コード desc;