WITH A AS(
SELECT PF_CODE, SUM(AMT) AS AMT
FROM DRINK_HABITS
WHERE CATEGORY_CODE = '120'
GROUP BY PF_CODE
),
B AS (
SELECT PF_CODE, SUM(AMT) AS AMT
FROM DRINK_HABITS
GROUP BY PF_CODE
)
SELECT
A.PF_CODE AS CODE,
PREFECTURE.PF_NAME AS NAME,
ROUND((A.AMT * 100.0)/ B.AMT, 2) AS PERCENTAGE
FROM A, PREFECTURE
INNER JOIN B
ON A.PF_CODE = B.PF_CODE
WHERE A.PF_CODE = PREFECTURE.PF_CODE
ORDER BY PERCENTAGE DESC,
CODE DESC;