with drink_rate as (
    select
        pf_code,
        round(cast(sum(
            case category_code 
            when '120' then amt
            else 0 end
        ) as real)/ sum(
            case category_code 
            when '110' then amt
            else 0 end
        )*100, 1) as percentage
    from drink_habits
    where gender_code in ('2', '3') and category_code
    group by pf_code
)
select
    dr.pf_code as CODE,
    pf_name as NAME,
    percentage as PERCENTAGE
from drink_rate as dr
left outer join prefecture as pf
    on dr.pf_code = pf.pf_code
order by PERCENTAGE desc, CODE desc