-- 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,
        p.pf_name as PREFECTURE,
        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
        left join
            prefecture as p
        on  e.pf_code = p.pf_code
    where
        e.SURVEY_YEAR = 2020
    --and p.pf_name in ('徳島県', '香川県')
    group by
        e.pf_code
),
result_01 as (
    select
        base.pf_code,
        base.PREFECTURE,
        1 as kind_order,
        '小学校' as KIND,
        base.ELEMENTARY as AMT
    from
        base
),
result_02 as (
    select
        base.pf_code,
        base.PREFECTURE,
        2 as kind_order,
        '中学校' as KIND,
        base.MIDDLE as AMT
    from
        base
),
result_03 as (
    select
        base.pf_code,
        base.PREFECTURE,
        3 as kind_order,
        '高校' as KIND,
        base.HIGH as AMT
    from
        base
),
result_04 as (
    select
        base.pf_code,
        base.PREFECTURE,
        4 as kind_order,
        '短大' as KIND,
        base.JUNIOR_CLG as AMT
    from
        base
),
result_05 as (
    select
        base.pf_code,
        base.PREFECTURE,
        5 as kind_order,
        '大学' as KIND,
        base.COLLEGE as AMT
    from
        base
),
result_06 as (
    select
        base.pf_code,
        base.PREFECTURE,
        6 as kind_order,
        '大学院' as KIND,
        base.GRADUATE as AMT
    from
        base
),
union_result as (
select 
    pf_code,
    kind_order,
    PREFECTURE, 
    KIND, 
    AMT 
from result_01 
union all select 
    pf_code,
    kind_order,
    PREFECTURE, 
    KIND, 
    AMT 
from result_02
union all select 
    pf_code,
    kind_order,
    PREFECTURE, 
    KIND, 
    AMT 
from result_03
union all select 
    pf_code,
    kind_order,
    PREFECTURE, 
    KIND, 
    AMT 
from result_04
union all select 
    pf_code,
    kind_order,
    PREFECTURE, 
    KIND, 
    AMT 
from result_05
union all select 
    pf_code,
    kind_order,
    PREFECTURE, 
    KIND, 
    AMT 
from result_06
)
select 
    2020 as SV_YEAR,
    PREFECTURE, 
    KIND, 
    AMT 
from union_result
where AMT >= 0
order by 
    pf_code asc, 
    kind_order asc;