with base as (
select SURVEY_YEAR,
       PF_CODE,
       "小学校" as KIND,
       total(ELEMENTARY) AMT,
       0 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
and ELEMENTARY is not null
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "中学校" as KIND,
       total(MIDDLE)  AMT,
       1 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
and MIDDLE is not null

group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "高校" as KIND,
       total(HIGH)  AMT,
       2 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020
and HIGH is not null

group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "短大" as KIND,
       total(JUNIOR_CLG) AMT,
       3 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020

and JUNIOR_CLG is not null
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "大学" as KIND,
       total(COLLEGE) AMT,
       4 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020

and COLLEGE is not null
group by SURVEY_YEAR,
       PF_CODE
UNION all 
select SURVEY_YEAR,
       PF_CODE,
       "大学院" as KIND,
       total(GRADUATE) AMT,
       5 as ord
from ENROLLMENT_STATUS
where SURVEY_YEAR = 2020

and GRADUATE is not null
group by SURVEY_YEAR,
       PF_CODE

)
select 
SURVEY_YEAR as SV_YEAR,
PF_NAME as PREFECTURE,
KIND,
AMT
 from base b
 inner join PREFECTURE p on p.pf_code = b.pf_code
order by b.PF_CODE, b.ord