SELECT
    PREFECTURE.PF_CODE AS CODE
    ,PREFECTURE.PF_NAME AS NAME
    ,ROUND(
        CAST(DRINK_POP AS REAL)
        / TOTAL_POP * 100
    , 1) AS PERCENTAGE
FROM
    (
        SELECT
            PF_CODE AS PF_HABITS_CODE
            ,SUM(CASE WHEN CATEGORY_CODE = "110" THEN AMT END) AS TOTAL_POP
            ,SUM(CASE WHEN CATEGORY_CODE = "120" THEN AMT END) AS DRINK_POP
        FROM
            DRINK_HABITS
        WHERE GENDER_CODE IN ("2", "3")
        GROUP BY
            PF_CODE
    )
    PREFECTURE
LEFT JOIN PREFECTURE ON PREFECTURE.PF_CODE = PF_HABITS_CODE
ORDER BY PERCENTAGE DESC, CODE DESC