select
d.PF_CODE AS CODE,
p.PF_NAME AS NAME,
round((cast((dm.AMT+dw.AMT)as real)/cast((d.AMT+w.AMT)as real))*100,1) AS PERCENTAGE
from DRINK_HABITS as d
join PREFECTURE as p on p.PF_CODE=d.PF_CODE

join DRINK_HABITS as w on w.CATEGORY_CODE="110" and w.GENDER_CODE="3" and w.PF_CODE=d.PF_CODE
join DRINK_HABITS as dm on dm.CATEGORY_CODE="120" and dm.GENDER_CODE="2" and dm.PF_CODE=d.PF_CODE
join DRINK_HABITS as dw on dw.CATEGORY_CODE="120" and dw.GENDER_CODE="3" and dw.PF_CODE=d.PF_CODE

where d.GENDER_CODE="2" and d.CATEGORY_CODE="110"
order by PERCENTAGE desc, d.PF_CODE desc
;