/*
SELECT 
    DH.*
    ,DH2.AMT AS "120AMT"
FROM DRINK_HABITS DH
INNER JOIN DRINK_HABITS DH2
ON DH.PF_CODE =DH2.PF_CODE
AND DH2.CATEGORY_CODE ="120"
WHERE DH.CATEGORY_CODE = "110"
AND DH.GENDER_CODE in ("2","3")
*/

SELECT
    --DH.CATEGORY_CODE
    DH.PF_CODE AS CODE
    ,PRE.PF_NAME AS NAME
    --,SUM(DH.AMT)
    --,MAX(TOTAL)
    ,ROUND(CAST(MAX(TOTAL) AS REAL)*100/CAST(SUM(DH.AMT) AS REAL),1) AS PERCENTAGE
FROM DRINK_HABITS DH
LEFT JOIN
(
    SELECT 
        PF_CODE
        ,SUM(AMT) AS TOTAL
    FROM DRINK_HABITS
    WHERE GENDER_CODE in ("2","3") and CATEGORY_CODE="120"
    GROUP BY PF_CODE
) SUB
ON DH.PF_CODE = SUB.PF_CODE
LEFT JOIN PREFECTURE PRE
ON DH.PF_CODE = PRE.PF_CODE
WHERE DH.CATEGORY_CODE="110"
GROUP BY DH.CATEGORY_CODE,DH.PF_CODE
ORDER BY PERCENTAGE DESC, DH.PF_CODE DESC