select
  pr.PF_CODE as CODE
 ,pr.PF_NAME as NAME
 ,ROUND(cast((dh2.AMT+dh3.AMT) as real)/cast(dh_sum.sum_amt as real)*100, 1) as PERCENTAGE
from 
  PREFECTURE pr
left join DRINK_HABITS dh2
  on pr.PF_CODE = dh2.PF_CODE
  and dh2.GENDER_CODE = '2'
  and dh2.CATEGORY_CODE = '120'
left join DRINK_HABITS dh3
  on pr.PF_CODE = dh3.PF_CODE
  and dh3.GENDER_CODE = '3'
  and dh3.CATEGORY_CODE = '120'
left join (
  select 
    PF_CODE,
    sum(AMT) as sum_amt
  from 
    DRINK_HABITS
  where 
    CATEGORY_CODE = '110'
  group by 
    PF_CODE
) dh_sum
  on pr.PF_CODE = dh_sum.PF_CODE
where 
  pr.PF_CODE = dh_sum.PF_CODE
order by 
  PERCENTAGE desc
 ,pr.PF_CODE desc
;