SELECT
    PF_CODE AS CODE,
    PF_NAME AS NAME,
    CAST(
            SUM(DRINK_HABITS.AMT) * 1000 / (TOTAL.AMT) + CASE
                WHEN CAST(SUM(DRINK_HABITS.AMT) AS REAL) / (TOTAL.AMT) * 1000 - SUM(DRINK_HABITS.AMT) * 1000 / (TOTAL.AMT) >= 0.5 THEN 1
                ELSE 0
            END
        AS REAL
    ) / 10.0 AS PERCENTAGE
FROM
    DRINK_HABITS
INNER JOIN
    PREFECTURE
    USING(PF_CODE)
LEFT JOIN (
        SELECT
            PF_CODE,
            SUM(AMT) AS AMT
        FROM
            DRINK_HABITS
        WHERE
            CATEGORY_CODE = 110
            AND (
                GENDER_CODE = 2
                OR GENDER_CODE = 3
            )
        GROUP BY
            PF_CODE
    ) AS TOTAL
    USING(PF_CODE)
WHERE
    CATEGORY_CODE = 120
    AND (
        GENDER_CODE = 2
        OR GENDER_CODE = 3
    )
GROUP BY
    PF_CODE
ORDER BY
    PERCENTAGE DESC,
    CODE DESC
;