WITH amt_columns AS (
    SELECT
        PF_CODE
        , SUM(CASE WHEN CATEGORY_CODE = 120 THEN AMT ELSE 0 END) AS everyday_amt
        , SUM(CASE WHEN CATEGORY_CODE = 110 THEN AMT ELSE 0 END) AS total_amt
    FROM
        DRINK_HABITS
    WHERE
        GENDER_CODE IN (2, 3)   -- 2:男,3:女
    GROUP BY
        PF_CODE
)
SELECT
    ac.PF_CODE AS CODE
    , PREFECTURE.PF_NAME AS NAME
    , ROUND((ac.everyday_amt * 100.0 / ac.total_amt), 1) AS PERCENTAGE
FROM
    amt_columns AS ac
INNER JOIN
    PREFECTURE ON ac.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
    PERCENTAGE DESC
    , CODE DesC
;