select
b.PORT_CODE as 港コード,
a.PORT_NAME as 港名,
SUM(CASE WHEN b.KIND_CODE = 110 THEN b.AMT ELSE 0 END)as 入国者数,
SUM(CASE WHEN b.KIND_CODE = 120 THEN b.AMT ELSE 0 END)as 出国者数,
SUM(CASE WHEN b.KIND_CODE = 110 THEN b.AMT ELSE 0 END)-
SUM(CASE WHEN b.KIND_CODE = 120 THEN b.AMT ELSE 0 END) as 差分
from
IMMIGRATION as b
INNER JOIN PORT as a
ON b.PORT_CODE = a.PORT_CODE
INNER JOIN GRP as c
ON b.GROUP_CODE = c.GROUP_CODE
where c.GROUP_CODE = '120' --外国人
group by b.PORT_CODE
having 入国者数>出国者数
order by 差分 desc,港コード desc;