select
sub.sv_year as SV_YEAR
,sub.PREFECTURE As PREFECTURE
,sub.kind as KIND
,sub.AMT as AMT
from(
select
e.survey_year as SV_YEAR
,p.pf_name as PREFECTURE
,'小学校' as KIND
,sum(e.elementary) as AMT
,'1' as rank
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year='2020'
group by sv_year,prefecture,kind
UNION ALL
select
e.survey_year as SV_YEAR
,p.pf_name as PREFECTURE
,'中学校' as KIND
,sum(e.middle) as AMT
,'2' as rank
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year='2020'
group by sv_year,prefecture,kind
UNION ALL
select
e.survey_year as SV_YEAR
,p.pf_name as PREFECTURE
,'高校' as KIND
,sum(e.high) as AMT
,'3' as rank
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year='2020'
group by sv_year,prefecture,kind
UNION ALL
select
e.survey_year as SV_YEAR
,p.pf_name as PREFECTURE
,'短大' as KIND
,sum(e.junior_clg) as AMT
,'4' as rank
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year='2020'
group by sv_year,prefecture,kind
UNION ALL
select
e.survey_year as SV_YEAR
,p.pf_name as PREFECTURE
,'大学' as KIND
,sum(e.college) as AMT
,'5' as rank
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year='2020'
group by sv_year,prefecture,kind
UNION ALL
select
e.survey_year as SV_YEAR
,p.pf_name as PREFECTURE
,'大学院' as KIND
,sum(graduate) as AMT
,'6' as rank
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year='2020'
group by sv_year,prefecture,kind
) as sub
inner join prefecture as pff
on pff.pf_name = sub.prefecture
where amt is not null
order by pff.pf_code asc,sub.rank asc