with rate as (
    select PF_CODE,
            sum(case when CATEGORY_CODE = '120' then AMT else 0 end) as "飲酒数",
            sum(AMT) as "母数"
    from DRINK_HABITS
    where GENDER_CODE IN ('1','2')
    group by PF_CODE
)

select PF_CODE as "CODE",
        PF_NAME as "NAME",
        round(cast(飲酒数 as real) * 100 / cast(母数 as real) ,1) as "PERCENTAGE"
from rate
    inner join PREFECTURE
    using(PF_CODE)
order by 3 desc,1 desc
;