with all_amt as (
    SELECT
        PF_CODE,
        SUM(AMT) as ALL_AMT
    FROM DRINK_HABITS
    where GENDER_CODE in (2, 3) and CATEGORY_CODE = 110
    group by PF_CODE
)

select
    PRE.PF_CODE as CODE,
    PRE.PF_NAME as NAME,
    round(cast(sum(AMT) as float) / cast(ALL_AMT as float) * 100, 1) as PERCENTAGE
from DRINK_HABITS as DH
left join PREFECTURE as PRE on DH.PF_CODE = PRE.PF_CODE
left join all_amt on DH.PF_CODE = all_amt.PF_CODE
where GENDER_CODE in (2, 3) and CATEGORY_CODE = 120
group by DH.PF_CODE
order by PERCENTAGE desc, PRE.PF_CODE desc