-- https://topsic-contest.jp/contests/contest002/problems/contest002-4
-- https://topsic-contest.jp/contests/contest002/problems/contest002-4
with base as(
    select
        e.pf_code,
        sum(case when ELEMENTARY is null then 0 else ELEMENTARY end) as ELEMENTARY,
        sum(case when MIDDLE is null then 0 else MIDDLE end) as MIDDLE,
        sum(case when HIGH is null then 0 else HIGH end) as HIGH,
        sum(case when JUNIOR_CLG is null then 0 else JUNIOR_CLG end) as JUNIOR_CLG,
        sum(case when COLLEGE is null then 0 else COLLEGE end) as COLLEGE,
        sum(case when GRADUATE is null then 0 else GRADUATE end) as GRADUATE
    from
        ENROLLMENT_STATUS as e
    where
        e.SURVEY_YEAR = 2020
    --and p.pf_name in ('徳島県', '香川県')
    group by
        e.pf_code
),
result_01 as (
    select
        base.pf_code,
        1 as kind_order,
        '小学校' as KIND,
        base.ELEMENTARY as AMT
    from
        base
),
result_02 as (
    select
        base.pf_code,
        2 as kind_order,
        '中学校' as KIND,
        base.MIDDLE as AMT
    from
        base
),
result_03 as (
    select
        base.pf_code,
        3 as kind_order,
        '高校' as KIND,
        base.HIGH as AMT
    from
        base
),
result_04 as (
    select
        base.pf_code,
        4 as kind_order,
        '短大' as KIND,
        base.JUNIOR_CLG as AMT
    from
        base
),
result_05 as (
    select
        base.pf_code,
        5 as kind_order,
        '大学' as KIND,
        base.COLLEGE as AMT
    from
        base
),
result_06 as (
    select
        base.pf_code,
        6 as kind_order,
        '大学院' as KIND,
        base.GRADUATE as AMT
    from
        base
),
union_result as (
select 
    pf_code, 1 as kind_order,  KIND,  AMT  from result_01 
union all select 
    pf_code, 2 as kind_order,  KIND,  AMT  from result_02
union all select 
    pf_code, 3 as kind_order,  KIND,  AMT  from result_03
union all select 
    pf_code, 4 as kind_order,  KIND,  AMT  from result_04
union all select 
    pf_code, 5 as kind_order,  KIND,  AMT  from result_05
union all select 
    pf_code, 6 as kind_order,  KIND,  AMT  from result_06
)
select 
    2020 as SV_YEAR,
    p.pf_name as PREFECTURE, 
    u.KIND, 
    u.AMT 
from union_result as u
    left join prefecture as p
    on u.pf_code = p.pf_code
where AMT > 0
order by 
    u.pf_code asc, 
    u.kind_order asc;