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