with population as (
    select
        sum(case when category_code = '110' then amt else 0 end) as total,
        sum(case when category_code = '120' then amt else 0 end) as drink,
        pf_code
    from
        drink_habits
    where
        gender_code in ('2', '3')
    group by
        pf_code
)
select
    population.pf_code as CODE,
    prefecture.pf_name as NAME,
    round(100.0 * population.drink / population.total, 1) as PERCENTAGE
from
    population
inner join
    prefecture
    on prefecture.pf_code = population.pf_code
order by
    percentage desc,
    code desc
;