with t1 as(
    select
        PF_CODE
        ,SUM(AMT) as x
    from
        DRINK_HABITS
    where
        GENDER_CODE in ('2', '3')
        AND
        CATEGORY_CODE = '120'
    group by 1
), t2 as (
    select
        PF_CODE
        ,SUM(AMT) as total
    from
        DRINK_HABITS
    where
        GENDER_CODE in ('2', '3')
        AND
        CATEGORY_CODE = '110'
    group by 1
)
select
    P.PF_CODE AS CODE
    ,P.PF_NAME AS NAME
    ,ROUND(100.0 * t1.x / t2.total, 1) AS PERCENTAGE
from
    PREFECTURE as P
inner join t1 on t1.PF_CODE = P.PF_CODE
inner join t2 on t2.PF_CODE = P.PF_CODE
order by 
    3 desc, 1 desc