select a.PF_CODE as CODE ,p.PF_NAME as NAME, round(b.TOTAL*100.0/a.TOTAL,1) as PERCENTAGE
from PREFECTURE p
join (
    select PF_CODE, TOTAL(AMT) as TOTAL from DRINK_HABITS WHERE CATEGORY_CODE = 110 AND GENDER_CODE IN('2','3') group by PF_CODE
) a on p.PF_CODE = a.PF_CODE
join (
    select PF_CODE, TOTAL(AMT) as TOTAL from DRINK_HABITS WHERE CATEGORY_CODE = 120 AND GENDER_CODE IN('2','3') group by PF_CODE
) b on p.PF_CODE = b.PF_CODE
order by PERCENTAGE desc, CODE desc;