WITH total AS (
    SELECT
        PF_CODE
        , SUM(AMT) AS total_amt
    FROM
        DRINK_HABITS
    WHERE
        GENDER_CODE IN (2, 3)   -- 2:男,3:女(1:不明)
        AND CATEGORY_CODE = 110 -- 総数
    GROUP BY
        PF_CODE
)
, everyday AS (
    SELECT
        PF_CODE
        , SUM(AMT) AS everyday_amt
    FROM
        DRINK_HABITS
    WHERE
        GENDER_CODE IN (2, 3)   -- 2:男,3:女(1:不明)
        AND CATEGORY_CODE = 120 -- 毎日飲酒
    GROUP BY
        PF_CODE
)
SELECT
    total.PF_CODE AS CODE
    , PREFECTURE.PF_NAME AS NAME
    , ROUND((everyday.everyday_amt * 100.0 / total.total_amt), 1) AS PERCENTAGE
FROM
    total
INNER JOIN
    everyday ON total.PF_CODE = everyday.PF_CODE
INNER JOIN
    PREFECTURE ON total.PF_CODE = PREFECTURE.PF_CODE
ORDER BY
    PERCENTAGE DESC
    , CODE DESC
;