-- select 
--     PF_CODE CODE,
--     PF_NAME NAME,
--     ROUND(
--         (select sum(AMT) from DRINK_HABITS dh where CATEGORY_CODE = '120' and GENDER_CODE IN ('2', '3') and dh.PF_CODE=p.PF_CODE)
--             *100.0
--             /(select sum(AMT) from DRINK_HABITS dh where CATEGORY_CODE = '110' and GENDER_CODE IN ('2', '3') and dh.PF_CODE=p.PF_CODE)
--         ,1
--     ) PERCENTAGE 
-- from Prefecture p
-- where PERCENTAGE IS NOT NULL
-- order by PERCENTAGE desc, CODE desc

select 
    p.PF_CODE CODE,
    p.PF_NAME NAME,
    ROUND(
        sum(dh2.AMT) * 100.0 / sum(dh1.AMT), 
        1
    ) PERCENTAGE
from
DRINK_HABITS dh1 
    inner join DRINK_HABITS dh2 on dh1.PF_CODE = dh2.PF_CODE 
    inner join PREFECTURE p on dh1.PF_CODE = p.PF_CODE
where dh1.CATEGORY_CODE = '110' 
    and dh2.CATEGORY_CODE = '120'
    and dh1.GENDER_CODE IN ('2', '3')
    and dh2.GENDER_CODE IN ('2', '3')
group by p.PF_CODE, p.PF_NAME
order by PERCENTAGE desc, CODE desc