SELECT
  PF_CODE AS CODE,
  PF_NAME AS NAME,
  COALESCE(round(100.00 * total_everyday_amt / total_amt, 1), 0) AS PERCENTAGE
FROM
  PREFECTURE
  LEFT JOIN (
    SELECT
      PF_CODE,
      SUM(
        CASE
          WHEN GENDER_CODE IN ('2', '3')
          AND CATEGORY_CODE = '120' THEN AMT
          ELSE 0
        END
      ) AS total_everyday_amt,
      SUM(
        CASE
          WHEN GENDER_CODE IN ('2', '3')
          AND CATEGORY_CODE = '110' THEN AMT
          ELSE 0
        END
      ) AS total_amt
    FROM
      DRINK_HABITS
    GROUP BY
      pf_code
  ) USING(PF_CODE)
ORDER BY
  1.00 * total_everyday_amt / total_amt DESC,
  pf_code DESC