select
    DH1.PF_CODE AS CODE
    ,PF.PF_NAME AS NAME
    ,ROUND(
            CAST(SUM(DH2.AMT)) /
            CAST(SUM(DH1.AMT))
            * 100
            ,1) AS PERCENTAGE
FROM DRINK_HABITS AS DH1
    INNER JOIN DRINK_HABITS AS DH2
        ON DH2.PF_CODE = DH1.PF_CODE
        AND DH2.CATEGORY_CODE = DH1.CATEGORY_CODE
        AND DH2.GENDER_CODE = DH1.GENDER_CODE
        AND DH2.GENDER_CODE <> '0'
        AND DH2.CATEGORY_CODE = '120'
    INNER JOIN PREFECTURE AS PF
        ON DH1.PF_CODE = PF.PF_CODE
        AND DH2.PF_CODE = PF.PF_CODE
WHERE DH1.GENDER_CODE <> '0'
GROUP BY DH1.PF_CODE
ORDER BY
    PERCENTAGE DESC
    DH1.PF_CODE DESC
;