SELECT d.PF_CODE AS CODE, p.PF_NAME AS NAME, ROUND((DRINK_AMT+0.0)*100.0/(TOTAL_AMT+0.0) ,1) AS PERCENTAGE
FROM DRINK_HABITS AS d 
JOIN PREFECTURE AS p ON d.PF_CODE=p.PF_CODE 
JOIN (
 SELECT PF_CODE, SUM(AMT) AS TOTAL_AMT
 FROM DRINK_HABITS
 WHERE CATEGORY_CODE='110' AND GENDER_CODE IN ('2','3')
 GROUP BY PF_CODE
) AS g110 ON d.PF_CODE=g110.PF_CODE 
JOIN (
 SELECT PF_CODE, SUM(AMT) AS DRINK_AMT
 FROM DRINK_HABITS
 WHERE CATEGORY_CODE='120' AND GENDER_CODE IN ('2','3')
 GROUP BY PF_CODE
) AS g120 ON d.PF_CODE=g120.PF_CODE 
GROUP BY CODE 
ORDER BY PERCENTAGE DESC, CODE DESC;