SELECT
    DH.PF_CODE AS 'CODE',
    PF_NAME AS 'NAME',
    ROUND((CAST(SUM(DH.AMT) AS REAL) / SUM(TOTAL.AMT)) * 100, 1) AS 'PERCENTAGE'
FROM DRINK_HABITS AS DH
    JOIN PREFECTURE AS PR
        ON DH.PF_CODE = PR.PF_CODE
    JOIN (SELECT PF_CODE, AMT FROM DRINK_HABITS WHERE CATEGORY_CODE = '110') AS TOTAL
        ON TOTAL.PF_CODE = DH.PF_CODE
WHERE
    CATEGORY_CODE = '120'
GROUP BY
    PF_NAME
ORDER BY
    PERCENTAGE DESC,
    CODE DESC