WITH ALLT AS(    
	select PF_CODE,sum(amt)AS AL     
	from DRINK_HABITS     
	where CATEGORY_CODE = '110' AND GENDER_CODE IN ('2','3')    
	group by PF_CODE    
),    
EVET AS(    
	select PF_CODE,sum(amt)AS EVE     
	from DRINK_HABITS     
	where CATEGORY_CODE = '120' AND GENDER_CODE IN ('2','3')    
	group by PF_CODE    
)    
SELECT DISTINCT    
	substr('00000'||D.PF_CODE, -5) AS CODE,    
	P.PF_NAME AS NAME,    
	ROUND(CAST(EVE AS REAL)/CAST(AL AS REAL)*100,1) AS PERCENTAGE    
FROM    
	DRINK_HABITS D     
	LEFT OUTER JOIN PREFECTURE P    
		ON D.PF_CODE = P.PF_CODE    
	LEFT OUTER JOIN ALLT A    
		ON D.PF_CODE = A.PF_CODE    
	LEFT OUTER JOIN EVET E    
		ON D.PF_CODE = E.PF_CODE    
ORDER BY     
	PERCENTAGE DESC,    
	CODE DESC