WITH total AS (
    SELECT
        PF_CODE
        , SUM(AMT) AS total_amt
    FROM
        DRINK_HABITS
    WHERE
        GENDER_CODE IN (2, 3)   -- 2='男',3='女'(1='不明')
    GROUP BY
        PF_CODE
)
, everyday AS (
    SELECT
        PF_CODE
        , SUM(AMT) AS everyday_total_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
    -- , total.total_amt   -- DEBUG
    -- , everyday.everyday_total_amt   -- DEBUG
    , ROUND((everyday.everyday_total_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 ASC
;