select
d.pf_code as CODE
,p.pf_name as NAME
,round(cast(sum(d.amt) as float)*100.0/cast(sum(total.amt)as float),1)as PERCENTAGE
from drink_habits as d
inner join prefecture as p
on d.pf_code = p.pf_code
inner join drink_habits as total
on total.pf_code = d.pf_code
and total.category_code='110'
and d.category_code ='120'
and total.gender_code = d.gender_code
and total.gender_code !='110'
group by code,name
order by PERCENTAGE desc,CODE desc