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',    
	CAST(ROUND(CAST(EVE AS decimal)/AL*100,1) AS decimal(10,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