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