select
    PREFECTURE.PF_CODE "CODE",
    PREFECTURE.PF_NAME "NAME",
    round(
        100. * sum(coalesce(everyday.AMT, 0)) / sum(coalesce(total.AMT, 0)),
        1
    ) "PERCENTAGE"
from
    PREFECTURE
    join DRINK_HABITS as everyday on everyday.PF_CODE = PREFECTURE.PF_CODE
    and everyday.CATEGORY_CODE = '120'
    join DRINK_HABITS as total on total.PF_CODE = PREFECTURE.PF_CODE
    and total.CATEGORY_CODE = '110'
    and total.GENDER_CODE = everyday.GENDER_CODE
where
    (
        total.GENDER_CODE = '2'
        or total.GENDER_CODE = '3'
    )
    and (
        everyday.GENDER_CODE = '2'
        or everyday.GENDER_CODE = '3'
    )
group by
    "CODE"
order by
    "PERCENTAGE" desc,
    "CODE" desc;