with all_num as(
select PF_CODE, sum(AMT) as total_num from DRINK_HABITS
--where CATEGORY_CODE = '110'
--    and GENDER_CODE != '1'
group by PF_CODE
), drink as(
select PF_CODE, sum(AMT) as drink_num from DRINK_HABITS
where GENDER_CODE in ('2', '3')
    and CATEGORY_CODE = '120'
group by PF_CODE
)
select p.PF_CODE as CODE, p.PF_NAME as NAME, round(cast(k.drink_num as REAL) / cast(a.total_num as REAL) * 100,1) as PERCENTAGE from PREFECTURE p
left join all_num a on p.PF_CODE = a.PF_CODE
left join drink k on p.PF_CODE = k.PF_CODE
order by PERCENTAGE desc, CODE desc
;