SELECT 
	 P.PF_CODE AS CODE
	,P.PF_NAME AS NAME
	,ROUND(1.0 * H.DRINK_TOTAL / H.TOTAL * 100, 1) AS PERCENTAGE
FROM 
	Prefecture P
INNER JOIN
	(
	SELECT
		PF_CODE
		,SUM(
			CASE Category_Code
			WHEN '110' THEN AMT
			ELSE 0
			END
		) AS TOTAL
		,SUM(
			CASE Category_Code
			WHEN '120' THEN AMT
			ELSE 0
			END
		) AS DRINK_TOTAL
	FROM 
		Drink_Habits
	WHERE
		Gender_Code in ('2', '3')
	GROUP BY
		PF_CODE
	) H
ON 
	P.PF_CODE = H.PF_CODE
ORDER BY
	 PERCENTAGE DESC
	,CODE DESC
;