with insyu as(
select
	PF_CODE as CODE
,	SUM(AMT) as 毎日飲酒人数
from
	DRINK_HABITS
where
	CATEGORY_CODE = '120'
and	GENDER_CODE in ('2','3')
group by
	PF_CODE
),
zeninn as(
select
	PF_CODE as CODE
,	SUM(AMT) as 総人数
from
	DRINK_HABITS
where
	CATEGORY_CODE = '110'
and	GENDER_CODE in ('2','3')
group by
	PF_CODE
)
select
	A.CODE
,	C.PF_NAME as NAME
,	ROUND(100.0 * A.毎日飲酒人数 / B.総人数,1) as PERCENTAGE
from
	insyu A
,	zeninn B
,	PREFECTURE C
where
	A.CODE = B.CODE
and	A.CODE = C.PF_CODE
order by
	PERCENTAGE DESC
,	A.CODE DESC