can you explain the grouping clause
A reader, January 13, 2002 - 11:23 am UTC
Tom what does the grouping clause do?
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
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.
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.
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.
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
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
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)
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.