SELECT DISTINCT
  PF_CODE AS "CODE",
  PF_NAME AS "NAME",
  ROUND(CAST(CATEGORY_AMT AS REAL) / CAST(TOTAL_AMT AS REAL) * 100,1) AS "PERCENTAGE"
FROM
  (
    SELECT
      dh.*,
      pr.PF_NAME,
      SUM(CASE WHEN dh.CATEGORY_CODE = '110' THEN AMT ELSE NULL END) OVER(PARTITION BY dh.PF_CODE) AS TOTAL_AMT,
      SUM(CASE WHEN dh.CATEGORY_CODE = '120' THEN AMT ELSE NULL END) OVER(PARTITION BY dh.PF_CODE) AS CATEGORY_AMT
    FROM
      DRINK_HABITS AS dh
    LEFT OUTER JOIN
      PREFECTURE AS pr
    ON
      dh.PF_CODE = pr.PF_CODE
    WHERE
      dh.GENDER_CODE IN ('2','3')
  )
ORDER BY
  "PERCENTAGE" desc,
  "CODE" desc