SELECT 
    CODE
    , NAME
    , round(sum(MAINICHI) * 1.0 / sum(TOTAL) * 100, 1) AS PERCENTAGE
FROM (
    SELECT
        PF_CODE AS CODE
        , PF_NAME AS NAME
        , CASE WHEN CATEGORY_CODE = '110' THEN AMT ELSE 0 END AS TOTAL
        , CASE WHEN CATEGORY_CODE = '120' THEN AMT ELSE 0 END AS MAINICHI
    FROM
        PREFECTURE
        INNER JOIN DRINK_HABITS
            USING (PF_CODE)
    WHERE
        GENDER_CODE IN ('2', '3')
    ) sub
GROUP BY
    CODE
    , NAME
ORDER BY
    PERCENTAGE DESC
    , CODE DESC
;