with a(SV_YEAR,PREFECTURE,KIND,AMT,PF_CODE,sq)
as (
select e.SURVEY_YEAR,p.PF_NAME,'小学校' as KIND,sum(e.ELEMENTARY),e.PF_CODE,1 as sq
from ENROLLMENT_STATUS e, PREFECTURE p
where e.PF_CODE = p.PF_CODE and e.SURVEY_YEAR = 2020 group by 1,2
union all
select e.SURVEY_YEAR,p.PF_NAME,'中学校' as KIND,sum(e.MIDDLE),e.PF_CODE,2 as sq
from ENROLLMENT_STATUS e, PREFECTURE p
where e.PF_CODE = p.PF_CODE and e.SURVEY_YEAR = 2020 group by 1,2
union all
select e.SURVEY_YEAR,p.PF_NAME,'高校' as KIND,sum(e.HIGH),e.PF_CODE,3 as sq
from ENROLLMENT_STATUS e, PREFECTURE p
where e.PF_CODE = p.PF_CODE and e.SURVEY_YEAR = 2020 group by 1,2
union all
select e.SURVEY_YEAR,p.PF_NAME,'短大' as KIND,sum(e.JUNIOR_CLG),e.PF_CODE,4 as sq
from ENROLLMENT_STATUS e, PREFECTURE p
where e.PF_CODE = p.PF_CODE and e.SURVEY_YEAR = 2020 group by 1,2
union all
select e.SURVEY_YEAR,p.PF_NAME,'大学' as KIND,sum(e.COLLEGE),e.PF_CODE,5 as sq
from ENROLLMENT_STATUS e, PREFECTURE p
where e.PF_CODE = p.PF_CODE and e.SURVEY_YEAR = 2020 group by 1,2
union all
select e.SURVEY_YEAR,p.PF_NAME,'大学院' as KIND,sum(e.GRADUATE),e.PF_CODE,6 as sq
from ENROLLMENT_STATUS e, PREFECTURE p
where e.PF_CODE = p.PF_CODE and e.SURVEY_YEAR = 2020 group by 1,2
)
select SV_YEAR,PREFECTURE,KIND,AMT from a
where AMT is not null
order by PF_CODE, sq