SELECT 
	 IN_GROUP.港コード 港コード
	,IN_GROUP.港名 港名
	,IN_GROUP.合計人数 AS 入国者数
	,OUT_GROUP.合計人数 AS 出国者数
	,(IN_GROUP.合計人数 - OUT_GROUP.合計人数) AS 差分
FROM
(
SELECT 
	 P.port_code AS 港コード
	,P.port_name AS 港名
	,SUM(
		CASE I.kind_code
			WHEN '110' THEN IFNULL(I.AMT,0)
		END
	) AS 合計人数
FROM
	Port P
INNER JOIN
	IMMIGRATION I	
ON
	P.port_code = I.port_code
INNER JOIN
	GRP G
ON
	G.group_code = I.group_code
GROUP BY
	P.port_code	
) AS IN_GROUP
INNER JOIN
(
SELECT 
	 P.port_code AS 港コード
	,P.port_name AS 港名
	,SUM(
		CASE I.kind_code
			WHEN '120' THEN IFNULL(I.AMT,0)
		END
	) AS 合計人数
FROM
	Port P
INNER JOIN
	IMMIGRATION I	
ON
	P.port_code = I.port_code
INNER JOIN
	GRP G
ON
	G.group_code = I.group_code
GROUP BY
	P.port_code	
) AS OUT_GROUP
ON
	IN_GROUP.港コード = OUT_GROUP.港コード
WHERE 差分 >= 0
ORDER BY
	 差分 DESC
	,IN_GROUP.港コード DESC