i want to make a chart by age group-CodePudding

member table

`select age, count(*) as "the number"
from (select floor((to_char(sysdate, 'YYYY') - substr(birth, 1, 4)) / 10) * 10 as age from member) 
group by age order by age asc;`

how to get data

when there is 10's data.

when there is no 10's data.

i want to print even when there is no data in 10's group.

for example,

age / the number

10 / 0

20 / 4

30 / 4

40 / 0

50 / 0

60 / 3

how can i do this?

CodePudding user response：

You can try this:

db<>fiddle

select grp.age, COALESCE(val."the number", 0) as "the number"
from (
    select 10 as age FROM dual UNION ALL
    select 20 FROM dual UNION ALL
    select 30 FROM dual UNION ALL
    select 40 FROM dual UNION ALL
    select 50 FROM dual UNION ALL
    select 60 FROM dual UNION ALL
    select 70 FROM dual UNION ALL
    select 80 FROM dual UNION ALL
    select 90 FROM dual UNION ALL
    select 100 FROM dual
) grp
left join (
    select age, count(*) as "the number"
    from (select floor((to_char(sysdate, 'YYYY') - substr(birth, 1, 4)) / 10) * 10 as age from member) 
    group by age 
) val ON grp.age = val.age
order by grp.age asc;