with everyday as (
    select
        d.PF_CODE,
        p.PF_NAME,
        sum(d.AMT) as AMT_sum
    from
        DRINK_HABITS d
    left join
        PREFECTURE p
    on d.PF_CODE = p.PF_CODE
    where
        GENDER_CODE <> '1' and
        CATEGORY_CODE = '120'
    group by 
        d.PF_CODE
)
select
    d.PF_CODE as CODE,
    e.PF_NAME as NAME,
    round(
    cast(e.AMT_sum as real) / sum(cast(d.AMT as real)) * 100 , 1
    ) as PERCENTAGE
from 
    DRINK_HABITS d
left join
    everyday e
on 
    d.PF_CODE = e.PF_CODE
group by 
    d.PF_CODE
order by
    PERCENTAGE desc,
    CODE asc;