with
base as(
select 
    DRINK_HABITS.PF_CODE
    ,PREFECTURE.PF_NAME
    ,sum(case when CATEGORY_CODE = '110' then AMT else 0 end) TOTAL_AMT
    ,sum(case when CATEGORY_CODE = '120' then AMT else 0 end) DRINK_AMT
from DRINK_HABITS
left join PREFECTURE using(PF_CODE) 
where GENDER_CODE IN(2,3)
group by
    PF_CODE
)
select 
    PF_CODE as "CODE"
    ,PF_NAME as "NAME"
    ,round(cast(DRINK_AMT*100 as float)/TOTAL_AMT, 1) as PERCENTAGE
from base
order by 
PERCENTAGE desc
,CODE desc
;