SELECT
	CODE as CODE
	,NAME as NAME
	,ROUND(CAST(mainichi as real)/cast(sousuu as real)*100,1)  PERCENTAGE
FROM
    (
        SELECT
        	PREFECTURE.PF_CODE as CODE
        	,PREFECTURE.PF_NAME as NAME
        	,CAST(SUM(CASE WHEN CATEGORY_CODE='110' THEN AMT ELSE 0 END) as real) as sousuu
        	,CAST(SUM(CASE WHEN CATEGORY_CODE='120' THEN AMT ELSE 0 END) as real) as mainichi
        FROM
        	PREFECTURE
        		LEFT JOIN	DRINK_HABITS
        			ON	PREFECTURE.PF_CODE = DRINK_HABITS.PF_CODE
        WHERE
            GENDER_CODE IN (2,3)
        GROUP BY 
        	PREFECTURE.PF_CODE
        	,PREFECTURE.PF_NAME
    )
ORDER BY 
    ROUND(CAST(mainichi as real)/cast(sousuu as real)*100,1) DESC
    ,CODE DESC