Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Puneet.

Asked: February 24, 2001 - 12:50 am UTC

Last updated: October 16, 2006 - 7:34 am UTC

Version: 8.1.5.0.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
How you doing there???? Well I wsih to know what's the problem between ROLLUP and CUBE functions when used in GROUP BY, as I used both of them but the output I got was same.
Please state with an example.

Regards
Puneet Mathur

and Tom said...

rollup will create subtotals, much like a CONTROL-BREAK report would. If you group by rollup(a,b,c), it'll produce subtotals by A,B and A. So, you get the results equivalent to:

select a,b,c, <aggregates>
from t
group by a,b,c
UNION ALL
select a,b,NULL,<aggregates>
from t
group by a,b,NULL
UNION ALL
select a,NULL,NULL,<aggregates>
from t
group by a,NULL,NULL
UNION ALL
select NULL,NULL,NULL,<aggregates>
from t
group by NULL,NULL,NULL
/

but they will be nicely ordered (as demonstrated below with the EMP table)


cube will create every combination -- you'll get the aggregates by:

a,b,c
a,b
a,c
b,c
a
b
c
<null>

Again, ordered nicely. Here is an example:


scott@ORA8I.WORLD> select decode(grouping(mgr),0,null,'summed') mgr_agg,
2 decode(grouping(job),0,null,'summed') job_agg,
3 mgr, job, sum(sal)
4 from emp
5 group by rollup(mgr,job)
6 /

MGR_AG JOB_AG MGR JOB SUM(SAL)
------ ------ ---------- --------- ----------
7566 ANALYST 6000
summed 7566 6000
7698 CLERK 950
7698 SALESMAN 5600
summed 7698 6550
7782 CLERK 1300
summed 7782 1300
7788 CLERK 1100
summed 7788 1100
7839 MANAGER 8275
summed 7839 8275
7902 CLERK 800
summed 7902 800
PRESIDENT 5000
5
summed 5005
summed summed 29030

17 rows selected.

scott@ORA8I.WORLD> select decode(grouping(mgr),0,null,'summed') mgr_agg,
2 decode(grouping(job),0,null,'summed') job_agg,
3 mgr, job, sum(sal)
4 from emp
5 group by cube(mgr,job)
6 /

MGR_AG JOB_AG MGR JOB SUM(SAL)
------ ------ ---------- --------- ----------
7566 ANALYST 6000
summed 7566 6000
7698 CLERK 950
7698 SALESMAN 5600
summed 7698 6550
7782 CLERK 1300
summed 7782 1300
7788 CLERK 1100
summed 7788 1100
7839 MANAGER 8275
summed 7839 8275
7902 CLERK 800
summed 7902 800
summed ANALYST 6000
summed CLERK 4150
summed MANAGER 8275
PRESIDENT 5000
summed PRESIDENT 5000
summed SALESMAN 5600
5
summed 5005
summed 5
summed summed 29030

23 rows selected.

scott@ORA8I.WORLD>

Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

can you explain the grouping clause

A reader, January 13, 2002 - 11:23 am UTC

Tom what does the grouping clause do?

Tom Kyte
January 13, 2002 - 1:01 pm UTC

well, its a documented function:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#77500 <code>

In short, if you look at this exmaple:


scott@ORA8I.WORLD> select decode(grouping(mgr),0,null,'summed') mgr_agg,
2 decode(grouping(job),0,null,'summed') job_agg,
3 mgr, job, sum(sal)
4 from emp
5 group by rollup(mgr,job)
6 /

MGR_AG JOB_AG MGR JOB SUM(SAL)
------ ------ ---------- --------- ----------
7566 ANALYST 6000
summed 7566 6000
7698 CLERK 950
7698 SALESMAN 5600
summed 7698 6550
....
....
summed summed 29030


we used the grouping function to determine what rows represented ROLLUPS -- when the columns we summed over. Here you see the row in bold with sum(sal) of 6550 -- we know thats a summary row -- summed OVER job for that manager. The last row in the result set is the total sum over both MGR and JOB -- and the grouping functions lets us identify it as such.

Ordering of grouping aggregate information

Martin, May 06, 2003 - 4:09 am UTC 

Hi Tom,

thanks for a great Oracle resource. 

I'm using 9.2.0.3.0 on win2000, and was wondering about the differences in the position of the CUBE and ROLLUP "totals".

Here's my table : 

SQL> DESC t;
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------
 A                                                                                                                          NUMBER
 B                                                                                                                          NUMBER

SQL> SELECT * FROM T;

         A          B
---------- ----------
         1         10
         2         20
         3         30
         4         40
         1         20

5 rows selected.

Now, the ROLLUP "totals" are (as you say), "nicely ordered" :

SQL> SELECT
  2  DECODE(GROUPING(A), 1, 'SUM OF B FOR ALL A') title1,
  3  DECODE(GROUPING(B), 1, 'SUM OF B FOR A = ' || a) title2,
  4  a, b, SUM(b)
  5  FROM t
  6  GROUP BY ROLLUP(A,B);

TITLE1             TITLE2                                                             A          B     SUM(B)
------------------ --------------------------------------------------------- ---------- ---------- ----------
                                                                                      1         10         10
                                                                                      1         20         20
                   SUM OF B FOR A = 1                                                 1                    30
                                                                                      2         20         20
                   SUM OF B FOR A = 2                                                 2                    20
                                                                                      3         30         30
                   SUM OF B FOR A = 3                                                 3                    30
                                                                                      4         40         40
                   SUM OF B FOR A = 4                                                 4                    40
SUM OF B FOR ALL A SUM OF B FOR A =                                                                       120

10 rows selected.

But the CUBE totals are inverted :

SQL> SELECT
  2  DECODE(GROUPING(A), 1, 'SUM OF B FOR ALL A') title1,
  3  DECODE(GROUPING(B), 1, 'SUM OF B FOR A = ' || a) title2,
  4  a, b, SUM(b)
  5  FROM t
  6   GROUP BY CUBE(a,b);

TITLE1             TITLE2                                                             A          B     SUM(B)
------------------ --------------------------------------------------------- ---------- ---------- ----------
SUM OF B FOR ALL A SUM OF B FOR A =                                                                       120
SUM OF B FOR ALL A                                                                              10         10
SUM OF B FOR ALL A                                                                              20         40
SUM OF B FOR ALL A                                                                              30         30
SUM OF B FOR ALL A                                                                              40         40
                   SUM OF B FOR A = 1                                                 1                    30
                                                                                      1         10         10
                                                                                      1         20         20
                   SUM OF B FOR A = 2                                                 2                    20
                                                                                      2         20         20
                   SUM OF B FOR A = 3                                                 3                    30
                                                                                      3         30         30
                   SUM OF B FOR A = 4                                                 4                    40
                                                                                      4         40         40

14 rows selected.

Does this imply, that you cannot guarantee the ORDER, like the selection of data, and have to use an ORDER BY to get the totals at the end?

Thanks in advance

Tom Kyte
May 06, 2003 - 7:58 am UTC

In a cube -- there is no "end".

rollup is sort of like "control break processing". it does a nice report.

cube -- it computes all aggregate combinations possible -- there is no concept of being "ordered" there in a report sense.


If you want a report -- rollup is natural.

If you need all aggregate combo's -- cube is natural and the grouping function will tell you in your program what each row represents. You can certainly order by it -- but you'll never get a "nice report looking report" out of it.

ROLLUP AND ORDERING

Asim Naveed, May 17, 2003 - 2:43 pm UTC

Hi,
I have read in oracle documentation that ORACLE does not
guarantee on the order of rows produced by a
SELECT statement until you use the ORDER BY CLAUSE.
So how can we rely on the order of rows produced by
ROLLUP without using ORDER BY.
for e.g

SELECT year, quarter, month, sum(sales)
FROM time
GROUP BY rollup(year,quarter,month)

Now I want rows to be returned by this query in the
natural hierarchy with totals in the right places.
will this be guaranteed without using ORDER BY. and if
we will have to use order by then please give the
order by clause for the above query.

Thanks.



Tom Kyte
May 17, 2003 - 3:31 pm UTC

the aggregate rows will appear where they belong --rollup does that. If you put an order by on it, the aggregated rows will not appear where they belong unless you take care to do so using the GROUPING() functions yourself.

read through the "sql for aggregation" chapter in the data warehousing guide for complete details on these functions.

... very interesting ...

Helena Marková, May 20, 2003 - 8:03 am UTC


rollup and order

Asim Naveed, May 24, 2003 - 4:02 pm UTC

I have read the sql for aggregation topic in oracle manuals.
There is not explicitly written that rollup will order
properly. Although all examples show the results
correctly ordered

But in SQL referrence manual its explicitly written
that ORACLE does not guarantee any particular order of
rows until you use ORDER BY clause. A query without
ORDER BY clause executed several times will/may
return rows in different orders.





Tom Kyte
May 25, 2003 - 9:21 am UTC

yes, but the subtotal rows will be in a predicable location -- at the group breaks. the order by can "destroy" this grouping, or you can use the grouping functions with order by to ensure that

a) the groups are returned to you in sorted order
b) the data within the groups are sorted
c) the subtotals remain in the correct location.

Group by nulls

A reader, July 27, 2003 - 1:49 pm UTC 

SQL> ed
Wrote file afiedt.buf

  1  select job,null,sum(sal)
  2  from emp1
  3  where job='ANALYST'
  4  group by job
  5  union
  6  select job,null,sum(sal)
  7  from emp1
  8  where job='SALESMAN'
  9* group by job
SQL> /

JOB       N   SUM(SAL)
--------- - ----------
ANALYST           6000
SALESMAN          5600

SQL> ed
Wrote file afiedt.buf

  1  select job,null,sum(sal)
  2  from emp1
  3  where job='ANALYST'
  4  group by job,null
  5  union
  6  select job,null,sum(sal)
  7  from emp1
  8  where job='SALESMAN'
  9* group by job
SQL> /

JOB       N   SUM(SAL)
--------- - ----------
ANALYST           6000
SALESMAN          5600



Tom 

in the example you have given above I dont think is is necessary to group by null.
select a,b,NULL,<aggregates>
  from t
 group by a,b,NULL

In you see my example, in the second select I removed null from the group by , but it did not fail the select.

Is it only for clarify that you have grouped by even nulls.

In addition to this sometime in group by's you see hard coded values with alias column names. For example...

select job,null,'H',sum(sal)
from emp1
where job='ANALYST'
group by job,null
union
select job,null,NULL,sum(sal)
from emp1
where job='SALESMAN'
group by job

Why is it not necessary to give the said 'H' status column ( what are these type of columns called) in the group by.

Tom Kyte
July 27, 2003 - 7:54 pm UTC

you don't need to group by constants -- it knows there is only "one" value.

Why the difference using an inline view?

Scott, October 01, 2003 - 2:23 pm UTC

Tom,

Can you explain what is happening here. The first query returns 1 row however when I wrap that inside another select the query returns 4 rows. ( I am using 9.2 )

scott@SWLOCAL> select 1,2,sum(1) from dual group by cube(1,2);

1 2 SUM(1)
---------- ---------- ----------
1 2 1

scott@SWLOCAL> select * from (select 1,2,sum(1) from dual group by cube(1,2));

1 2 SUM(1)
---------- ---------- ----------
1 2 1
1 2 1
1 2 1
1 2 1


Thanks

Tom Kyte
October 01, 2003 - 2:32 pm UTC

it is a strange effect of the optimzer "knowing" dual should have one row. it was not expecting the group by cube to explode the data.

Funny, because under TOAD (Oracle 9.2.0.4)...

Franco, October 02, 2003 - 3:01 am UTC

... the two queries give the same result: 4 rows. (?!?!)


Yeah, I wondered about that too....

Martin, November 21, 2003 - 10:05 am UTC 

Tom,

at what point does the "optimizer / dual" thing happen? 

Since, even the entries in v$sqltext are the same for a TOAD-issued query, than for a SQL*Plus issued query....

SQL> select sql_text
  2  from v$sqltext
  3  where sql_text like '%testsql%'
  4  /

SQL_TEXT
----------------------------------------------------------------------
select /* testsql from SQL*Plus */ 1 from dual group by cube (1,2,3)
select /* testsql from TOAD */ 1 from dual group by cube (1,2,3)

Regards
Martin

different level of aggregation

dheeraj, October 04, 2005 - 7:33 am UTC

Hi,

does this means by using roleup/cube we are allowing different level of aggregation in the same query,this is in reference to the Data Warehouse.

Thanks

Tom Kyte
October 04, 2005 - 4:24 pm UTC

you are able to get any level of aggregation - yes.

A trick I have developed.

Ghalib Hamidi, October 15, 2006 - 1:59 pm UTC

You could use lower case z to order. Example:
select decode(mgr, 'zzzzzz','Total_mgrs',mgr),
decode(job, 'zzz','Total_jobs',jobs),
sal
from (
select DECODE(GROUPING(mgr), 1, 'zzzzzz',mgr) AS mgr,
DECODE(GROUPING(job), 1, 'zzz',job) AS job,
sum(sal) sal
From emp
GROUP BY CUBE (mgr, job)
order by 1,2)

Tom Kyte
October 16, 2006 - 7:34 am UTC

(nls issues of course, you must have a good idea of your character set...)

RE : Why the difference using an inline view?

J.Rajeshwaran, August 04, 2008 - 9:49 am UTC

Both the queries returns the same result

select 1,2,sum(1) from dual group by cube(1,2);
&
select * from (select 1,2,sum(1) from dual group by cube(1,2));
Here is the Answer for your question. (why it return's 4 records ?)

The logic with CUBE is Simple if your CUBE operator has N expression's then it will produce 2^N ie for N=3 you will have 2^3 = 8 Groupings in your result set's. And hence in this Case you have 2 Expression so you have 4 result Sets.
simillary the below query will produce 2^3 = 8 Result Sets.
SELECT 'A','B','C',SUM(1) FROM DUAL GROUP BY
CUBE ('A','B','C')

Similarly for ROLLUP Operator the Formula is N+1 for (ie for N=3 this will produce 4 Grouping's)
The below Query will produce 4 record set's
SELECT 'A','B','C',SUM(1)
FROM DUAL
GROUP BY ROLLUP('A','B','C')

Am i right TOM ? Please correct me if i am wrong.

Many Thanks,
Rajesh.


RE : Why the difference using an inline view?

J.Rajeshwaran, August 04, 2008 - 10:12 am UTC

Please ignore my previous one i have wrongly used RECORDS it should be like GROUPINGS.
----------------------------------------------------------
Both the queries returns the same result

select 1,2,sum(1) from dual group by cube(1,2);
&
select * from (select 1,2,sum(1) from dual group by cube(1,2));
Here is the Answer for your question. (why it return's 4 records ?)

The logic with CUBE is Simple if your CUBE operator has N expression's then it will produce 2^N ie
for N=3 you will have 2^3 = 8 Groupings in your result set's. And hence in this Case you have 2
Expression so you have 4 Grouping's Sets.
simillary the below query will produce 2^3 = 8 Grouping'sSets.
SELECT 'A','B','C',SUM(1) FROM DUAL GROUP BY
CUBE ('A','B','C')

Similarly for ROLLUP Operator the Formula is N+1 for (ie for N=3 this will produce 4 Grouping's)
The below Query will produce 4 Grouping'sset's
SELECT 'A','B','C',SUM(1)
FROM DUAL
GROUP BY ROLLUP('A','B','C')

Am i right TOM ? Please correct me if i am wrong.

Many Thanks,
Rajesh.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.