WITH drink as (
    SELECT PF_CODE
           ,SUM(AMT) AS AMT
      FROM DRINK_HABITS
     WHERE CATEGORY_CODE = '120'
       AND GENDER_CODE IN ('2', '3')
     GROUP BY PF_CODE
   ),
   total as(
       SELECT PF_CODE
             ,SUM(AMT) AS AMT
      FROM DRINK_HABITS
     WHERE CATEGORY_CODE = '110'
       AND GENDER_CODE IN ('2', '3')
     GROUP BY PF_CODE
)
SELECT PR.PF_CODE AS CODE
      ,PR.PF_NAME AS NAME
      ,ROUND(cast(drink.AMT AS REAL) / cast(total.AMT AS REAL) * 100, 1) AS PERCENTAGE
  FROM PREFECTURE PR
  LEFT JOIN drink
    ON PR.PF_CODE = drink.PF_CODE
  LEFT JOIN total
    ON PR.PF_CODE = total.PF_CODE
  WHERE PERCENTAGE IS NOT NULL
ORDER BY PERCENTAGE DESC
        ,CODE DESC