-- survey_year = 2020
-- nullは表示対称としない
select
SV_YEAR,
PREFECTURE,
KIND,
AMT
from
(select
survey_year as SV_YEAR,
e.pf_code,
pf_name as PREFECTURE,
"1" as kind_code,
"小学校" as KIND,
sum(elementary) as AMT
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year = 2020
and elementary is not null
group by e.pf_code
union 
select
survey_year as SV_YEAR,
e.pf_code,
pf_name as PREFECTURE,
"2" as kind_code,
"中学校" as KIND,
sum(MIDDLE) as AMT
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year = 2020
and middle is not null
group by e.pf_code
union 
select
survey_year as SV_YEAR,
e.pf_code,
pf_name as PREFECTURE,
"3" as kind_code,
"高校" as KIND,
sum(HIGH) as AMT
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year = 2020
and high is not null
group by e.pf_code
union 
select
survey_year as SV_YEAR,
e.pf_code,
pf_name as PREFECTURE,
"4" as kind_code,
"短大" as KIND,
sum(JUNIOR_CLG) as AMT
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year = 2020
and JUNIOR_CLG is not null
group by e.pf_code
union 
select
survey_year as SV_YEAR,
e.pf_code,
pf_name as PREFECTURE,
"5" as kind_code,
"大学" as KIND,
sum(COLLEGE) as AMT
from enrollment_status as e
inner join prefecture as p
on e.pf_code = p.pf_code
where survey_year = 2020
and COLLEGE is not null
group by e.pf_code)
order by pf_code,kind_code