with
man_total as (select * from DRINK_HABITS where CATEGORY_CODE = 110 and GENDER_CODE = 2),
woman_total as (select * from DRINK_HABITS where CATEGORY_CODE = 110 and GENDER_CODE = 3),
man_drink as (select * from DRINK_HABITS where CATEGORY_CODE = 120 and GENDER_CODE = 2),
woman_drink as (select * from DRINK_HABITS where CATEGORY_CODE = 120 and GENDER_CODE = 3)
select
PREFECTURE.PF_CODE as CODE,
PREFECTURE.PF_NAME as NAME,
round(cast(man_drink.AMT + woman_drink.AMT as REAL) / cast(man_total.AMT + woman_total.AMT as REAL) * 100, 1) as PERCENTAGE
from
PREFECTURE
join man_total on man_total.PF_CODE = PREFECTURE.PF_CODE
join woman_total on woman_total.PF_CODE = PREFECTURE.PF_CODE
join man_drink on man_drink.PF_CODE = PREFECTURE.PF_CODE
join woman_drink on woman_drink.PF_CODE = PREFECTURE.PF_CODE
order by PERCENTAGE desc, CODE desc