select
    p.PF_CODE AS CODE,
    p.PF_NAME as NAME,
    round(cast(drinker.AMT as real) / cast(total.AMT as real) * 100, 1) as PERCENTAGE
from
    PREFECTURE p
    join
        (
            select
                PF_CODE,
                sum(AMT) as AMT
            from
                DRINK_HABITS
            where
                CATEGORY_CODE = 110
            AND GENDER_CODE <> 1
            group by
                PF_CODE
        ) total
    on  p.PF_CODE = total.PF_CODE
    join
        (
            select
                PF_CODE,
                sum(AMT) as AMT
            from
                DRINK_HABITS
            where
                CATEGORY_CODE = 120
            AND GENDER_CODE <> 1
            group by
                PF_CODE
        ) drinker
    on  p.PF_CODE = drinker.PF_CODE
order by
    PERCENTAGE DESC,
    CODE DESC