WITH CD110 AS(
SELECT
    PF_CODE AS CODE
    , CATEGORY_CODE
    , sum(AMT) AS AMT
FROM DRINK_HABITS
WHERE GENDER_CODE IN ('2', '3')
    AND CATEGORY_CODE IN ('110')
GROUP BY CATEGORY_CODE, PF_CODE
)
, CD120 AS(
SELECT
    PF_CODE AS CODE
    , CATEGORY_CODE
    , sum(AMT) AS AMT
    -- , 都道府県毎の毎日飲酒率 AS PERCENTAGE
FROM DRINK_HABITS
WHERE GENDER_CODE IN ('2', '3')
    AND CATEGORY_CODE IN ('120')
GROUP BY CATEGORY_CODE, PF_CODE
)

SELECT 
    CD110.CODE AS CODE
    , P.PF_NAME AS NAME
    , ROUND(CAST(CD120.AMT AS DOUBLE) / 
    CAST(CD110.AMT AS DOUBLE) * 100, 1) AS PERCENTAGE
FROM CD110 JOIN CD120
    ON CD110.CODE = CD120.CODE
    LEFT OUTER JOIN PREFECTURE AS P
        ON CD110.CODE = P.PF_CODE
ORDER BY PERCENTAGE DESC, CODE DESC