SELECT
    dh.PF_CODE AS CODE
    ,pr.PF_NAME AS NAME
    ,ROUND((100.0 * sum(dh.AMT) / sum_X.s_AMT),1) AS PERCENTAGE
FROM
    DRINK_HABITS dh
    INNER JOIN
        PREFECTURE pr
            ON dh.PF_CODE = pr.PF_CODE
                AND dh.GENDER_CODE IN ('2','3')
                AND dh.CATEGORY_CODE = '120'
    INNER JOIN
        (
            SELECT
                dh.PF_CODE
                ,SUM(dh.AMT) AS s_AMT
            FROM
                DRINK_HABITS dh
            WHERE
                dh.GENDER_CODE IN ('2','3')
                AND dh.CATEGORY_CODE = '110'
            GROUP BY
                dh.PF_CODE
        ) sum_X
            ON dh.PF_CODE = sum_X.PF_CODE
GROUP BY
    CODE
    ,NAME
ORDER BY
    PERCENTAGE DESC
    ,CODE DESC