select
    SV_YEAR,
    PREFECTURE,
    KIND,
    AMT
from(
select
    e.survey_year as SV_YEAR,
    p.pf_code as CODE,
    p.pf_name as PREFECTURE,
    '1' as KIND_NO,
    '小学校' as KIND,
    sum(e.elementary) as AMT
from
    enrollment_status as e left join prefecture as p on e.pf_code = p.pf_code
where
    e.survey_year = 2020
group by
    e.survey_year, p.pf_name
union all
select
    e.survey_year as SV_YEAR,
    p.pf_code as CODE,
    p.pf_name as PREFECTURE,
    '2' as KIND_NO,
    '中学校' as KIND,
    sum(e.middle) as AMT
from
    enrollment_status as e left join prefecture as p on e.pf_code = p.pf_code
where
    e.survey_year = 2020
group by
    e.survey_year, p.pf_name
union all
select
    e.survey_year as SV_YEAR,
    p.pf_code as CODE,
    p.pf_name as PREFECTURE,
    '3' as KIND_NO,
    '高校' as KIND,
    sum(e.high) as AMT
from
    enrollment_status as e left join prefecture as p on e.pf_code = p.pf_code
where
    e.survey_year = 2020
group by
    e.survey_year, p.pf_name
union all
select
    e.survey_year as SV_YEAR,
    p.pf_code as CODE,
    p.pf_name as PREFECTURE,
    '4' as KIND_NO,
    '短大' as KIND,
    sum(e.junior_clg) as AMT
from
    enrollment_status as e left join prefecture as p on e.pf_code = p.pf_code
where
    e.survey_year = 2020
group by
    e.survey_year, p.pf_name
union all
select
    e.survey_year as SV_YEAR,
    p.pf_code as CODE,
    p.pf_name as PREFECTURE,
    '5' as KIND_NO,
    '大学' as KIND,
    sum(e.college) as AMT
from
    enrollment_status as e left join prefecture as p on e.pf_code = p.pf_code
where
    e.survey_year = 2020
group by
    e.survey_year, p.pf_name
union all
select
    e.survey_year as SV_YEAR,
    p.pf_code as CODE,
    p.pf_name as PREFECTURE,
    '6' as KIND_NO,
    '大学院' as KIND,
    sum(e.graduate) as AMT
from
    enrollment_status as e left join prefecture as p on e.pf_code = p.pf_code
where
    e.survey_year = 2020
group by
    e.survey_year, p.pf_name
)
where
    amt is not null
order by
    CODE, KIND_NO
;