WITH total AS 
(
 SELECT
  a.PF_CODE AS CODE,
  b.PF_NAME AS NAME,
  SUM(CAST(a.AMT AS FLOAT)) AS AMT
 FROM
  DRINK_HABITS AS a
 LEFT JOIN
  PREFECTURE AS b
 USING
  (PF_CODE)
 WHERE
  CATEGORY_CODE = '110'
 AND
  GENDER_CODE <> '1'
 GROUP BY
  CODE,NAME
),
total_drink AS 
(
 SELECT
  a.PF_CODE AS CODE,
  b.PF_NAME AS NAME,
  SUM(CAST(a.AMT AS FLOAT)) AS AMT
 FROM
  DRINK_HABITS AS a
 LEFT JOIN
  PREFECTURE AS b
 USING
  (PF_CODE)
 WHERE
  CATEGORY_CODE = '120'
 AND
  GENDER_CODE <> '1'
 GROUP BY
  CODE,NAME
)
SELECT
 a.CODE AS CODE,
 a.NAME AS NAME,
 ROUND(100*(b.AMT/a.AMT),1) AS PERCENTAGE
FROM
 total AS a
LEFT JOIN
 total_drink AS b
USING
 (CODE)
ORDER BY
 PERCENTAGE DESC,CODE DESC