select
pre.PF_CODE as 'CODE'
,pre.PF_NAME as 'NAME'
,ROUND(100.0*(drm.AMT + drf.AMT)/(drma.AMT + drfa.AMT),1) as 'PERCENTAGE'
from
DRINK_HABITS drm
inner join
PREFECTURE pre
on
drm.PF_CODE = pre.PF_CODE
inner join
DRINK_HABITS drf
on
drm.PF_CODE = drf.PF_CODE
and
drf.CATEGORY_CODE = 120
and
drf.GENDER_CODE = 3
inner join
DRINK_HABITS drma
on
drm.PF_CODE = drma.PF_CODE
and
drma.CATEGORY_CODE = 110
and
drma.GENDER_CODE = 2
inner join
DRINK_HABITS drfa
on
drm.PF_CODE = drfa.PF_CODE
and
drfa.GENDER_CODE = 3
and
drfa.CATEGORY_CODE = 110
where
drm.CATEGORY_CODE = 120
and
drm.GENDER_CODE = 2
order by
PERCENTAGE desc
,CODE desc