with t0 as 
(
    select 
        pf_code as CODE, CATEGORY_CODE, GENDER_CODE, 
        AMT, 
        case when CATEGORY_CODE='110' then AMT else NULL end as total, 
        case when CATEGORY_CODE='120' then AMT else NULL end as inshu
        from DRINK_HABITS
        where category_code!='130' and (gender_code='2' or gender_code='3')
)
, t1 as 
(
    select 
        pf_code, pf_name as NAME
        from PREFECTURE
)
, t2_0 as 
(
    select 
        CODE, NAME, total, inshu
        from t0
        inner join t1
        on t0.CODE=t1.pf_code
)
, t2 as 
(
    select 
        CODE, NAME, 
        sum(total) as total, 
        sum(inshu) as inshu
        from t2_0
        group by CODE, NAME
)
, t3 as 
(
    select 
        CODE, NAME, 
        round(inshu*1.0 / (total*1.0) *100, 1) as PERCENTAGE
        from t2
)
select * from t3 order by PERCENTAGE desc, CODE desc
--select * from t3