WITH

-- 総数
SOSUU AS (
 SELECT
   SUM(T1.AMT) AS S_SUM
  ,T1.PF_CODE
  ,T2.PF_NAME
 FROM
  DRINK_HABITS T1
 LEFT JOIN  PREFECTURE T2
 ON
  T1.PF_CODE = T2.PF_CODE
 WHERE
  T1.GENDER_CODE IN ('2', '3')
 AND
  T1.CATEGORY_CODE = '110'
 GROUP BY
   T1.PF_CODE
  ,T2.PF_NAME
)

-- 毎日
,MAINICHI AS (
 SELECT
   SUM(T1.AMT) AS M_SUM
  ,T1.PF_CODE
  ,T2.PF_NAME
 FROM
  DRINK_HABITS T1
 LEFT OUTER JOIN  PREFECTURE T2
 ON
  T1.PF_CODE = T2.PF_CODE
 WHERE
  T1.GENDER_CODE IN ('2', '3')
 AND
  T1.CATEGORY_CODE = '120'
 GROUP BY
   T1.PF_CODE
  ,T2.PF_NAME
)

SELECT
  T1.PF_CODE AS CODE
 ,T1.PF_NAME AS NAME
 ,((T1.M_SUM / T2.S_SUM) * 100.0) AS PERCENTAGE
FROM
 MAINICHI T1
LEFT JOIN SOSUU T2
ON
 T1.PF_CODE = T2.PF_CODE