with base_tbl as
(
select
  PF_CODE
  ,sum(case when CATEGORY_CODE = 110 then AMT else null end) as total
  ,sum(case when CATEGORY_CODE = 120 then AMT else null end) as drink
from DRINK_HABITS
where GENDER_CODE in (2,3)
group by 1
)
select
  a.PF_CODE as CODE
  ,b.PF_NAME as NAME
  ,round(100.0*a.drink/a.total,1) as PERCENTAGE
from base_tbl a
inner join PREFECTURE b
on a.PF_CODE=b.PF_CODE
order by PERCENTAGE desc, CODE desc