with
drink as(
    select
        PF_CODE
        , sum(AMT) as pop
    from
        DRINK_HABITS
    where
        GENDER_CODE in (2, 3)
        and CATEGORY_CODE = 120
    group by PF_CODE
),
total as(
    select
        PF_CODE
        , sum(AMT) as pop
    from
        DRINK_HABITS
    where
        GENDER_CODE in (2, 3)
        and CATEGORY_CODE = 110
    group by PF_CODE
)
select
    d.PF_CODE as CODE
    , p.PF_NAME as NAME 
    , ROUND( 
        -- 計算項目がINTEGER型なのでCAST関数でREAL型に変換して計算しています
        CAST(d.pop AS REAL) /CAST(t.pop AS REAL) * 100
        , 1
        ) as PERCENTAGE
from drink as d
inner join total as t
on d.PF_CODE = t.PF_CODE
inner join PREFECTURE as p
on d.PF_CODE = p.PF_CODE
order by PERCENTAGE desc,
        d.PF_CODE asc