WITH T1 AS (
SELECT DR.PF_CODE,
    SUM(CASE NDR.CATEGORY_CODE WHEN '110' THEN NDR.AMT ELSE 0 END) AS '総数',
    SUM(CASE DR.CATEGORY_CODE WHEN '120' THEN DR.AMT ELSE 0 END) AS '毎日飲酒'
FROM DRINK_HABITS AS DR INNER JOIN DRINK_HABITS AS NDR
    ON NDR.PF_CODE=DR.PF_CODE AND NDR.GENDER_CODE<>'1'
    WHERE DR.GENDER_CODE<>'1'
GROUP BY DR.PF_CODE
)
SELECT PF.PF_CODE AS CODE,
    PF.PF_NAME AS NAME,
    ROUND(AVG(毎日飲酒 * 100.0 / 総数),1) AS PERCENTAGE
FROM PREFECTURE AS PF
INNER JOIN T1 ON PF.PF_CODE=T1.PF_CODE
GROUP BY PF.PF_CODE
ORDER BY PERCENTAGE DESC,CODE DESC