with vRate as
(select
    d.PF_CODE
    ,cast(sum(case when d.CATEGORY_CODE = '110' then d.AMT else 0 end)as real) SOUSUU
    ,cast(sum(case when d.CATEGORY_CODE = '120' then d.AMT else 0 end)as real) MAINICHI
    from DRINK_HABITS d
    where d.GENDER_CODE in ('2','3')
    group by
    d.PF_CODE
    having 
    sum(case when d.CATEGORY_CODE = '120' then d.AMT else 0 end)<>0)
select
 r.PF_CODE as CODE
,pf.PF_NAME as NAME
,round(r.MAINICHI*100/r.SOUSUU,1) as PERCENTAGE
from vRate r
inner join PREFECTURE pf
    on r.PF_CODE = pf.PF_CODE
order by 
 round(r.MAINICHI*100/r.SOUSUU,1) desc,r.PF_CODE desc