I am using a rollup to get some aggregates and display them to the user in tabular form.
However, I'd like to ensure that within my rollup the number of rows that are rolled up is the same i.e. the number of the largest subset.
I think an example makes what I want much clearer, so I setup a simple example in oracle below:
create table test (
co_name varchar2(100),
rtype number,
some_count number
) ;
insert all
into test (co_name, rtype, some_count) values ('A', 1, 5)
into test (co_name, rtype, some_count) values ('A', 2, 6)
into test (co_name, rtype, some_count) values ('A', 3, 7)
into test (co_name, rtype, some_count) values ('B', 1, 8)
into test (co_name, rtype, some_count) values ('B', 2, 9)
SELECT * FROM DUAL
;
select * from test;
SELECT
co_name,
rtype,
count(some_count)
FROM test
GROUP BY ROLLUP(co_name, rtype)
This gives me the following results :
CO_NAME RTYPE SOME_COUNT
A 1 5
A 2 6
A 3 7
A 18
B 1 8
B 2 9
B 17
B 35
You'll notice of course that B only has two rows for RTYPE - 1 and 2 That is because there are 0 rows where CO_NAME = B AND RTYPE = 3
Is there a way to keep rollup consistent in the number of results it returns? What I'd like is to see the following:
CO_NAME RTYPE SOME_COUNT
A 1 5
A 2 6
A 3 7
A 18
B 1 8
B 2 9
B 3 0
B 17
35
Any suggestions here will be very helpful, as I'd like my application to be stupid and just tabulate the results without having to account for missing data. I'd like the query to give me everything I need.
Thanks!
EDIT: I am a dope... In my sample above I wanted to keep things simple but made a mistake. Instead of RTYPES being a set of {1,2,3} possible values, imagine it is a set of {'x','y','z'} possible values... I did mark an answer as the answer because it answered the question I asked, and the fault is on me :(
rollup clause will not fill in gaps, you have to do it beforehand:
SQL> with rtypes(col) as(
2 select level
3 from ( select max(count(co_name)) as mx_num
4 from test1
5 group by co_name
6 ) t
7 connect by level <= t.mx_num
8 )
9 select t.co_name
10 , r.col as rtype
11 , sum(nvl(some_count, 0)) as some_count
12 from test1 t
13 partition by (t.co_name)
14 right join rtypes r
15 on (r.col = t.rtype)
16 group by rollup(t.co_name, r.col)
17 ;
Result:
Co_Name rtype Some_Count
--------------------------------------
A 1 5
A 2 6
A 3 7
A 18
B 1 8
B 2 9
B 3 0
B 17
35
The query in the WITH clause is used to generate RTYPES from 1 to 3 (as it happens 3
is the maximum number for rtype in this case). In the main query we right outer join our
TEST1 table with actual data with RTYPES CTE using partition by() clause.
Find out more about partition join.
Answer to the comment:
If there characters, as you said one-character value(x, y, z or a, b, c it
doesn't really matter), in the RTYPE column, We can rewrite the CTE(the query in the WITH
clause) to generate set of characters as follows:
with rtypes(col) as(
select chr(min_char + level - 1)
from ( select max(ascii(rtype1)) as max_char
, min(ascii(rtype1)) as min_char
from test1
)
connect by min_char + level <= max_char + 1
)
And then the final query will be:
with rtypes(col) as(
select chr(min_char + level - 1)
from ( select max(ascii(rtype1)) as max_char
, min(ascii(rtype1)) as min_char
from test1
)
connect by min_char + level <= max_char + 1
)
select t.co_name
, r.col as rtype
, sum(nvl(some_count, 0)) as some_count
from test1 t
partition by (t.co_name)
right join rtypes r
on (r.col = t.rtype1)
group by rollup(t.co_name, r.col)
Result:
Co_Name rtype Some_Count
--------------------------------------
A x 5
A y 6
A z 7
A 18
B x 8
B y 9
B z 0
B 17
35