SELECT
AMTTOTAL.PF_CODE AS CODE,
PREFECTURE.PF_NAME AS NAME,

round((cast(AMTTOTAL.AMT as real) / cast(TOTAL.AMT as real )) *100 ,1)AS PERCENTAGE

FROM 
(
SELECT 
DRINK_HABITS.PF_CODE,
SUM(DRINK_HABITS.AMT) AS AMT
FROM DRINK_HABITS
WHERE CATEGORY_CODE = '120' AND (GENDER_CODE = '2' OR GENDER_CODE = '3')
GROUP BY PF_CODE
) AMTTOTAL
INNER JOIN PREFECTURE ON PREFECTURE.PF_CODE  = AMTTOTAL.PF_CODE
INNER JOIN (
SELECT 
DRINK_HABITS.PF_CODE,
SUM(DRINK_HABITS.AMT) AS AMT
FROM 
DRINK_HABITS
WHERE CATEGORY_CODE = '110'
GROUP BY PF_CODE
) TOTAL ON TOTAL.PF_CODE = AMTTOTAL.PF_CODE

ORDER BY PERCENTAGE desc,PREFECTURE.PF_CODE DESC