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