select
    total.pf_code AS CODE
    ,total.pf_name AS NAME
    ,ROUND(dr.drinkers/total.total*100,2) AS PERCENTAGE
from prefecture pf
    inner join
    (
        select dh.pf_code,pf.pf_name,count(dh.amt) AS total
        from prefecture pf
            left join drink_habits dh on dh.pf_code=pf.pf_code
        group by dh.pf_code
    ) total on pf.pf_code=total.pf_code
    inner join (
        select count(amt) as drinkers, pf_code
        from drink_habits dh
        where category_code='120' and gender_code in ('2','3')
        group by pf_code
    ) dr pf.pf_code = dr.pf_code
order by ROUND(dr.drinkers/total.total*100,2) desc, total.pf_name desc
;