with n as (
select pf_code, sum(AMT) as sa
from drink_habits
where gender_code in (2, 3) and category_code = 120
group by pf_code
)
, t as (
select pf_code, sum(AMT) as sa
from drink_habits
where gender_code in (2, 3) and category_code = 110
group by pf_code
)

select n.pf_code as CODE, prefecture.pf_name as NAME
,ROUND(100.0 *n.sa / t.sa, 1) as PERCENTAGE from n join t on n.pf_code = t.pf_code join prefecture on n.pf_code = prefecture.pf_code
order by PERCENTAGE desc, code desc