select
AA.PF_CODE AS CODE,
C.PF_NAME AS NAME,
ROUND(CAST(BB.AMT AS REAL) / CAST(AA.AMT AS REAL) *100,1) AS PERCENTAGE
from
(
select PF_CODE,SUM(AMT) AS AMT from DRINK_HABITS A
where A.GENDER_CODE in ('2','3') and A.CATEGORY_CODE='110'
group by A.PF_CODE
) AA
inner join
(
select PF_CODE,SUM(AMT) AS AMT from DRINK_HABITS B
where B.GENDER_CODE in ('2','3') and B.CATEGORY_CODE='120'
group by B.PF_CODE
) BB
on
AA.PF_CODE=BB.PF_CODE
inner join PREFECTURE C
on
AA.PF_CODE=C.PF_CODE
order by PERCENTAGE desc,CODE desc
;