SELECT
    d.PF_CODE AS "CODE",
    p.PF_NAME AS "NAME",
    ROUND(CAST(search.AMT AS REAL) / CAST(SUM(d.AMT) AS REAL) * 100,1)  AS "PERCENTAGE"
FROM
    DRINK_HABITS AS d
    JOIN PREFECTURE AS p ON d.PF_CODE = p.PF_CODE
    JOIN (SELECT PF_CODE, SUM(AMT) AS "AMT" FROM DRINK_HABITS WHERE CATEGORY_CODE = "120" AND GENDER_CODE IN ("2","3") GROUP BY PF_CODE) AS search ON search.PF_CODE = d.PF_CODE 
WHERE
    d.CATEGORY_CODE = "110"
    AND d.GENDER_CODE IN ("2","3")
GROUP BY
    d.PF_CODE
ORDER BY
    "PERCENTAGE" DESC,
    "CODE" DESC