select
 DRINK_HABITS.PF_CODE as CODE
,PF_NAME as NAME
,round(Mainichi.AMT / cast(sum(DRINK_HABITS.AMT) as REAL) *100,1) as PERCENTAGE
from
 DRINK_HABITS
inner join(
 select
  PF_CODE
 ,cast(sum(AMT) as real) as AMT
 from
  DRINK_HABITS
 where
  GENDER_CODE in (2,3)
  and CATEGORY_CODE = 120
 group by
  PF_CODE
)as Mainichi
on DRINK_HABITS.PF_CODE = Mainichi.PF_CODE
inner join
 PREFECTURE
 on DRINK_HABITS.PF_CODE = PREFECTURE.PF_CODE
where
 GENDER_CODE in (2,3)
 and CATEGORY_CODE=110
group by
 CODE
,NAME 
order by
 PERCENTAGE desc
,CODE desc