select
    p.pf_code as CODE,
    p.pf_name as NAME,
    round(sum(d2.amt)*100.0/sum(d.amt),1) as PERCENTAGE
from
    DRINK_HABITS as d
    inner join PREFECTURE as p on p.PF_CODE=d.PF_CODE
    inner join DRINK_HABITS as d2 on p.PF_CODE=d2.PF_CODE
where
    d.CATEGORY_CODE=110 and d2.CATEGORY_CODE=120
group by p.pf_code
order by PERCENTAGE desc,code desc
;