WITH
TOTAL AS
(
    SELECT
        D.PF_CODE   AS CODE
    ,   SUM(D.AMT)  AS TOTAL_AMT
    FROM
        DRINK_HABITS D
    INNER JOIN
        PREFECTURE P 
        ON D.PF_CODE = P.PF_CODE
    WHERE
        D.CATEGORY_CODE = '110'
    AND D.GENDER_CODE IN ('2', '3')
    GROUP BY
        D.PF_CODE
),
EVERYDAY AS
(
    SELECT
        D.PF_CODE   AS CODE
    ,   SUM(D.AMT)  AS TOTAL_AMT
    FROM
        DRINK_HABITS D
    INNER JOIN
        PREFECTURE P 
        ON D.PF_CODE = P.PF_CODE
    WHERE
        D.CATEGORY_CODE = '120'
    AND D.GENDER_CODE IN ('2', '3')
    GROUP BY
        D.PF_CODE
)
SELECT
    P.PF_CODE AS CODE
,   P.PF_NAME AS NAME
,   ROUND(CAST(IFNULL(E.TOTAL_AMT, 0) AS REAL) / CAST(IFNULL(T.TOTAL_AMT, 1) AS REAL) * 100, 1) AS PERCENTAGE
FROM
    PREFECTURE P
LEFT JOIN
    TOTAL T
    ON P.PF_CODE = T.CODE
LEFT JOIN
    EVERYDAY E
    ON P.PF_CODE = E.CODE
ORDER BY
    PERCENTAGE DESC
,   CODE DESC