select
	d.PF_CODE as CODE
	,p.PF_NAME as NAME
	,round(cast(d.DRINK as real) / d.TOTAL * 100, 1) as PERCENTAGE
from
	(
		select
			sum(case when CATEGORY_CODE = '120' then AMT else 0 end) as DRINK
			,sum(case when CATEGORY_CODE = '110' then AMT else 0 end) as TOTAL
			,PF_CODE
		from
			DRINK_HABITS
		where
			GENDER_CODE in ('2', '3')
		group by
			PF_CODE
	) as d
	left join PREFECTURE as p
		on d.PF_CODE = p.PF_CODE
order by
	3 desc, 1 desc