SELECT
	IMMIGRATION.PORT_CODE AS '港コード',
    PORT_NAME AS '港名',
    AVG(I) AS '入国者数',
    AVG(O) AS '出国者数',
    AVG(I - O) AS '差分'
FROM IMMIGRATION
JOIN PORT
	ON IMMIGRATION.PORT_CODE = PORT.PORT_CODE
JOIN GRP
    ON IMMIGRATION.GROUP_CODE = GRP.GROUP_CODE
JOIN (SELECT PORT_CODE, SUM(AMT) AS 'I' FROM IMMIGRATION WHERE KIND_CODE = 110 GROUP BY PORT_CODE) AS INBOUND
    ON IMMIGRATION.PORT_CODE = INBOUND.PORT_CODE
JOIN (SELECT PORT_CODE, SUM(AMT) AS 'O' FROM IMMIGRATION WHERE KIND_CODE = 120 GROUP BY PORT_CODE) AS OUTBOUND
    ON IMMIGRATION.PORT_CODE = OUTBOUND.PORT_CODE
WHERE
    GROUP_NAME = '外国人'
    AND I - O > 0
GROUP BY
    IMMIGRATION.PORT_CODE
ORDER BY
    I - O desc,
    IMMIGRATION.PORT_CODE desc