select distinct a.PF_CODE as CODE, PREFECTURE.PF_NAME as NAME, round(cast(DRINK_TOTAL as real) / cast(ALL_TOTAL as real) * 100, 1) as PERCENTAGE from DRINK_HABITS
 left join (select CATEGORY_CODE, PF_CODE, sum(AMT) as DRINK_TOTAL from DRINK_HABITS where CATEGORY_CODE = '120' and not GENDER_CODE = '1' group by PF_CODE) a on a.PF_CODE = DRINK_HABITS.PF_CODE
 left join (select CATEGORY_CODE, PF_CODE, sum(AMT) as ALL_TOTAL from DRINK_HABITS where CATEGORY_CODE = '110' and not GENDER_CODE = '1' group by PF_CODE) b on b.PF_CODE = DRINK_HABITS.PF_CODE
 left join PREFECTURE on PREFECTURE.PF_CODE = DRINK_HABITS.PF_CODE
 order by PERCENTAGE desc, PREFECTURE.PF_CODE desc;