with t as (
    select CATEGORY_CODE, d.PF_CODE, pf_name, sum(amt) as s
    from DRINK_HABITS as d
    inner join PREFECTURE as p on d.pf_code=p.pf_code
    where d.GENDER_CODE !=1
    group by CATEGORY_CODE, d.PF_CODE,pf_name
)
select t1.PF_CODE as "CODE", t1.pf_name as "NAME", round(t2.s * 100.0 / t1.s,1) as "PERCENTAGE"
from t as t1 inner join t as t2 on t1.PF_CODE=t2.PF_CODE and t2.CATEGORY_CODE=120
where t1.CATEGORY_CODE=110
order by 3 desc, 1 desc