with time_120 as(
    select
        TIME_CODE, 
        AGE_CODE,
        sum(TARGET_POP) as TARGET_POP
    from
        SLEEP_TIME_DTL
    where
        TIME_CODE = 120
    group by TIME_CODE, AGE_CODE
),
time_130 as(
    select
        TIME_CODE, 
        AGE_CODE,
        sum(TARGET_POP) as TARGET_POP
    from
        SLEEP_TIME_DTL
    where
        TIME_CODE = 130
    group by TIME_CODE, AGE_CODE
),
time_140 as(
    select
        TIME_CODE, 
        AGE_CODE,
        sum(TARGET_POP) as TARGET_POP
    from
        SLEEP_TIME_DTL
    where
        TIME_CODE = 140
    group by TIME_CODE, AGE_CODE
),
time_150 as(
    select
        TIME_CODE, 
        AGE_CODE,
        sum(TARGET_POP) as TARGET_POP
    from
        SLEEP_TIME_DTL
    where
        TIME_CODE = 150
    group by TIME_CODE, AGE_CODE
),
time_160 as(
    select
        TIME_CODE, 
        AGE_CODE,
        sum(TARGET_POP) as TARGET_POP
    from
        SLEEP_TIME_DTL
    where
        TIME_CODE = 160
    group by TIME_CODE, AGE_CODE
),
time_170 as(
    select
        TIME_CODE, 
        AGE_CODE,
        sum(TARGET_POP) as TARGET_POP
    from
        SLEEP_TIME_DTL
    where
        TIME_CODE = 170
    group by TIME_CODE, AGE_CODE
),
time_180 as(
    select
        TIME_CODE, 
        AGE_CODE,
        sum(TARGET_POP) as TARGET_POP
    from
        SLEEP_TIME_DTL
    where
        TIME_CODE = 180
    group by TIME_CODE, AGE_CODE
)

select
    --age.AGE_CODE,
    age.AGE_NAME as '年齢階層',
    time_120.target_pop as '5時間未満',
    time_130.target_pop as '5時間以上6時間未満',
    time_140.target_pop as '6時間以上7時間未満',
    time_150.target_pop as '7時間以上8時間未満',
    time_160.target_pop as '8時間以上9時間未満',
    time_170.target_pop as '9時間以上',
    time_180.target_pop as '不詳'	
from
    AGE_GRP age
    left join time_120 
    on age.age_code = time_120.AGE_CODE
    left join time_130 
    on age.age_code = time_130.AGE_CODE
    left join time_140 
    on age.age_code = time_140.AGE_CODE
    left join time_150 
    on age.age_code = time_150.AGE_CODE
    left join time_160 
    on age.age_code = time_160.AGE_CODE
    left join time_170 
    on age.age_code = time_170.AGE_CODE
    left join time_180 
    on age.age_code = time_180.AGE_CODE
;