select dr.PF_CODE as CODE
       , pr.PF_NAME as NAME
       , round(cast(oo.合計 as REAL )/cast(gg.合計 as REAL)* 100,1) AS PERCENTAGE
    
from DRINK_HABITS as dr

inner join PREFECTURE as pr
on dr.PF_CODE = pr.PF_CODE

INNER JOIN (select sum(AMT) AS 合計
                   ,PF_CODE
            FROM DRINK_HABITS
            where CATEGORY_CODE = '110'
            and GENDER_CODE IN (2,3)
            group by PF_CODE)AS gg
        ON gg.PF_CODE = dr.PF_CODE 
        
INNER JOIN (select sum(AMT) AS 合計
                   ,PF_CODE
            FROM DRINK_HABITS
            where CATEGORY_CODE = '120'
            and GENDER_CODE IN (2,3)
            group by PF_CODE)AS oo
        ON oo.PF_CODE = dr.PF_CODE 
        
group by dr.PF_CODE 
       , pr.PF_NAME 
order by dr.PF_CODE desc,CODE desc