select
SURVEY_YEAR as SV_YEAR
,PREFECTURE.PF_NAME
,test as AMT
from
(
select
SURVEY_YEAR
,PF_CODE
,1 as rank
,sum(cast(ELEMENTARY as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE

UNION 

select
SURVEY_YEAR
,PF_CODE
,2 as rank
,sum(cast(MIDDLE as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE

UNION 

select
SURVEY_YEAR
,PF_CODE
,3 as rank
,sum(cast(HIGH as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE

UNION 

select
SURVEY_YEAR
,PF_CODE
,4 as rank
,sum(cast(JUNIOR_CLG as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE

UNION 

select
SURVEY_YEAR
,PF_CODE
,5 as rank
,sum(cast(COLLEGE as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE

UNION 

select
SURVEY_YEAR
,PF_CODE
,6 as rank
,sum(cast(GRADUATE as REAL)) as test
from
ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
group by PF_CODE
) aaa inner join PREFECTURE on 
aaa.PF_CODE = PREFECTURE.PF_CODE
where test is not null;