with drunker_by_pref as (
    select
        DRINK_HABITS.PF_CODE,
        sum( case DRINK_HABITS.CATEGORY_CODE when '110' then AMT else 0 end ) as total,
        sum( case DRINK_HABITS.CATEGORY_CODE when '120' then AMT else 0 end ) as drunker
    from
        DRINK_HABITS
    where
        GENDER_CODE in ('2', '3')
    group by DRINK_HABITS.PF_CODE
) select
    drunker_by_pref.PF_CODE as CODE,
    PREFECTURE.PF_NAME as NAME,
    round( cast( drunker_by_pref.drunker as real ) / cast( drunker_by_pref.total as real ) * 100, 1 ) as PERCENTAGE
from
    drunker_by_pref
    join PREFECTURE on PREFECTURE.PF_CODE = drunker_by_pref.PF_CODE
order by PERCENTAGE desc, CODE desc
;