sql - Ensuring same number of rows in oracle rollup -

i using rollup aggregates , display them user in tabular form.

however, i'd ensure within rollup number of rows rolled same i.e. number of largest subset.

i think example makes want clearer, setup simple example in oracle below:

create table test (     co_name varchar2(100),     rtype number,     some_count number )         ; insert     test (co_name, rtype, some_count) values ('a', 1, 5)     test (co_name, rtype, some_count) values ('a', 2, 6)     test (co_name, rtype, some_count) values ('a', 3, 7)     test (co_name, rtype, some_count) values ('b', 1, 8)     test (co_name, rtype, some_count) values ('b', 2, 9) select * dual ;  select * test; select     co_name,     rtype,     count(some_count) test group rollup(co_name, rtype) 

this gives me following results :

co_name rtype   some_count       1       5       2       6       3       7               18 b       1       8 b       2       9 b               17 b               35 

you'll notice of course b has 2 rows rtype - 1 , 2 because there 0 rows co_name = b , rtype = 3

is there way keep rollup consistent in number of results returns? i'd see following:

co_name rtype   some_count       1       5       2       6       3       7               18 b       1       8 b       2       9 b       3       0 b               17                 35 

any suggestions here helpful, i'd application stupid , tabulate results without having account missing data. i'd query give me need.

thanks!

edit: dope... in sample above wanted keep things simple made mistake. instead of rtypes being set of {1,2,3} possible values, imagine set of {'x','y','z'} possible values... did mark answer answer because answered question asked, , fault on me :(

rollup clause not fill in gaps, have beforehand:

sql> rtypes(col) as(   2    select level   3      ( select max(count(co_name)) mx_num   4               test1   5              group co_name   6            ) t   7    connect level <= t.mx_num   8  )   9  select t.co_name  10       , r.col                   rtype  11       , sum(nvl(some_count, 0)) some_count  12    test1 t  13    partition (t.co_name)  14    right join rtypes r  15       on (r.col = t.rtype)  16  group rollup(t.co_name, r.col)  17  ; 

result:

co_name rtype   some_count --------------------------------------        1       5        2       6        3       7                18  b       1       8  b       2       9  b       3       0  b               17                  35  

the query in with clause used generate rtypes 1 3 (as happens 3

is maximum number rtype in case). in main query right outer join our

test1 table actual data rtypes cte using partition by() clause.

find out more partition join.

---

answer comment:

if there characters, said one-character value(x, y, z or a, b, c it

doesn't matter), in rtype column, can rewrite cte(the query in with

clause) generate set of characters follows:

with rtypes(col) as(   select chr(min_char + level - 1)    ( select max(ascii(rtype1)) max_char                , min(ascii(rtype1)) min_char             test1          )   connect min_char + level <=  max_char + 1 ) 

and final query be:

with rtypes(col) as(     select chr(min_char + level - 1)       ( select max(ascii(rtype1)) max_char                   , min(ascii(rtype1)) min_char               test1            )     connect min_char + level <=  max_char + 1   ) select t.co_name      , r.col                   rtype      , sum(nvl(some_count, 0)) some_count   test1 t   partition (t.co_name)   right join rtypes r      on (r.col = t.rtype1)   group rollup(t.co_name, r.col) 

result:

co_name rtype   some_count --------------------------------------        x       5        y       6        z       7                18  b       x       8  b       y       9  b       z       0  b               17                  35