with drink as (
    select
        PF_CODE,
        CATEGORY_CODE,
        AMT
    from
        DRINK_HABITS
    where
        GENDER_CODE != "1"
    and
        CATEGORY_CODE = 120
    group by
        PF_CODE,
        CATEGORY_CODE
)

select
    p.PF_CODE as CODE,
    p.PF_NAME as NAME,
    round(100 * cast(d2.AMT as real)/cast(d.AMT as real),2) as PERCENTAGE
from
    PREFECTURE as p
join
    DRINK_HABITS as d on d.PF_CODE = p.PF_CODE
join
    drink as d2 on d2.PF_CODE = p.PF_CODE
where
    d.GENDER_CODE != "1"
and
    d.CATEGORY_CODE = 110
group by
    CODE,d.CATEGORY_CODE
order by
    PERCENTAGE desc,
    CODE desc