WITH DRINK_SUM AS (
    SELECT
        PF_CODE
        ,SUM(CASE WHEN CATEGORY_CODE='120' THEN AMT ELSE 0 END) AS DRINK_AMT
        ,SUM(CASE WHEN CATEGORY_CODE='110' THEN AMT ELSE 0 END) AS TOTAL_AMT
    FROM DRINK_HABITS
    WHERE
        GENDER_CODE IN ('2', '3')
    GROUP BY PF_CODE
)
SELECT
    ds.PF_CODE AS CODE
    ,pr.PF_NAME AS NAME
    ,ROUND((ds.DRINK_AMT / CAST(ds.TOTAL_AMT AS FLOAT))*100, 1) AS PERCENTAGE 
FROM DRINK_SUM ds
INNER JOIN PREFECTURE pr
    ON ds.PF_CODE = pr.PF_CODE
ORDER BY
    PERCENTAGE DESC,
    ds.PF_CODE DESC;