WITH ALL_DATA AS(
    WITH EVERYDAY AS(
        SELECT
            PF_CODE,
            SUM(AMT) AS AMT
        FROM
            DRINK_HABITS
        WHERE
            CATEGORY_CODE = '120'
            AND GENDER_CODE != '1'
        GROUP BY
            PF_CODE
    )
    SELECT
        DRINK_HABITS.PF_CODE,
        SUM(DRINK_HABITS.AMT) AS TOTAL_AMT,
        EVERYDAY.AMT AS EVERYDAY_AMT
    FROM
        DRINK_HABITS
        INNER JOIN
            EVERYDAY ON EVERYDAY.PF_CODE = DRINK_HABITS.PF_CODE
    WHERE
        CATEGORY_CODE = '110'
        AND GENDER_CODE != '1'
    GROUP BY
        DRINK_HABITS.PF_CODE
)
SELECT
    ALL_DATA.PF_CODE AS CODE,
    PREFECTURE.PF_NAME AS NAME,
    ROUND(CAST(EVERYDAY_AMT AS FLOAT) / CAST(TOTAL_AMT AS FLOAT) * 100.0, 1) AS PERCENTAGE
FROM
    ALL_DATA
    INNER JOIN
        PREFECTURE ON PREFECTURE.PF_CODE = ALL_DATA.PF_CODE
ORDER BY
    PERCENTAGE DESC,
    CODE DESC