WITH
D1 AS
(SELECT PF_CODE,SUM(AMT) AMT
 FROM DRINK_HABITS
 WHERE GENDER_CODE <>'1'
 AND CATEGORY_CODE = '120'
 GROUP BY PF_CODE
),
D2 AS 
(SELECT PF_CODE,SUM(AMT) AMT
 FROM DRINK_HABITS
 WHERE GENDER_CODE <>'1'
 AND CATEGORY_CODE = '110'
 GROUP BY PF_CODE
)
SELECT
 P.PF_CODE  CODE
,P.PF_NAME  NAME
,ROUND(CAST(IFNULL(D1.AMT,0) AS REAL)/CAST(D2.AMT AS REAL) *100,1) PERCENTAGE
FROM
D2
INNER JOIN
PREFECTURE P
ON D2.PF_CODE = P.PF_CODE
LEFT OUTER JOIN
D1
ON D2.PF_CODE = D1.PF_CODE
ORDER BY 
3 DESC,1
;