A reader, May 07, 2003 - 10:30 am UTC
but want something more
chirayu sutaria, May 08, 2003 - 1:08 am UTC
Dear Tom,
Thanks for the prompt reply..
but I think I was not able to articulate my question properly..
moreover I am also learning the analytics, rollup, cube,
so not well equipped to apply these advanced features appropriately.
Please help me out.
If you would look at my query again..
SELECT vplcode, SUM(rated_flat_amount)/COUNT(*) amt
FROM tap_rtx_009
WHERE vplcode = 223
GROUP BY vplcode, start_d_t, actual_volume,
rated_flat_amount
HAVING COUNT(*) > 1
I have four fields in my group by clause,
so when I modified my query ..
SELECT grouping(vplcode), SUM(rated_flat_amount)/COUNT(*) amt
FROM tap_rtx_009
WHERE vplcode = 223
GROUP BY rollup(vplcode, start_d_t, actual_volume,
rated_flat_amount)
HAVING COUNT(*) > 1 AND grouping(vplcode) = 1
I got the result set as
GROUPING(VPLCODE) AMT
1 7.48142587770495
I think I had made some mistake somewhere..
Please also see that I have four columns in the group by clause
and I am filtering the query to return only those rows who have a count greater
than one..
Hope this clarifies my doubt...
May 08, 2003 - 9:38 am UTC
add the other columns in (i've never understood why they wouldn't be there)
select out their grouping() values as well
use that output to clearly identify the rows you want.
use that in the having clause. Believe it or not, that is what I do -- i always forget if 0 is aggregates and 1's are details or vice versa -- so I run it, isolate the rows I want -- add the predicate and away you go
What difference does it make when.....
pasko, May 08, 2003 - 2:56 am UTC
Hi Tom ,
Thanks for a great trick above for filtering unwanted rows :
"having grouping(deptno) = 1 OR
(grouping(deptno)+grouping(job) = 0)"
I have tried this Rollup stuff myself and i have a question related slightly to this :
1.
Is the order of columns in the rollup statement related
in any way to the group by columns from the select list ?
2.I note that i get different results when i reverse the order of columns inside the rollup Brackets ..is this expected behavior ?
For Example :
These queries don't seem to return same Results :
select grouping(deptno), grouping(job),
deptno, job ,
sum(sal) amt
from emp
group by ROLLUP(deptno, job)
select grouping(deptno), grouping(job),
deptno, job ,
sum(sal) amt
from emp
group by ROLLUP( job , deptno ) -- reversed order of columns
select grouping(deptno), grouping(job),
job , deptno , -- reversed order of columns
sum(sal) amt
from emp
group by ROLLUP( job , deptno )
sorry i don't have scott sample schema setup right here with me. :-(
Thanks in advance.
May 08, 2003 - 9:46 am UTC
1) 100%
2) yes. The first rollup(deptno,job) will sort (rollup sorts) by deptno, job -- giving you subtotals by job within deptno and by deptno and totals for the entire set.
rollup(job,deptno) will sort by job, deptno -- giving you subtotals by deptno within job and then by job and then totals
scott@ORA817DEV> select deptno, job, sum(sal),
2 decode( grouping(job),1,'<===job'),
3 decode( grouping(deptno),1,'<===deptno')
4 from emp group by rollup(deptno,job)
5 /
DEPTNO JOB SUM(SAL) DECODE( DECODE(GRO
---------- --------- ---------- ------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <===job
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875 <===job
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <===job
29025 <===job <===deptno
13 rows selected.
scott@ORA817DEV> select deptno, job, sum(sal),
2 decode( grouping(job),1,'<===job'),
3 decode( grouping(deptno),1,'<===deptno')
4 from emp group by rollup(job,deptno)
5 /
DEPTNO JOB SUM(SAL) DECODE( DECODE(GRO
---------- --------- ---------- ------- ----------
20 ANALYST 6000
ANALYST 6000 <===deptno
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150 <===deptno
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
MANAGER 8275 <===deptno
10 PRESIDENT 5000
PRESIDENT 5000 <===deptno
30 SALESMAN 5600
SALESMAN 5600 <===deptno
29025 <===job <===deptno
15 rows selected.
everybody that has sqlplus has emp and dept. $ORACLE_HOME/sqlplus/demo/demobld.sql. you can put it into your own schema, doesn't have to be SCOTT
Thanks very much...mostly appreciated ....
A reader, May 09, 2003 - 3:19 am UTC
anything new in 9i
john, April 05, 2004 - 11:14 pm UTC
Tom,
in the example quoted below,
select deptno, job, sum(sal),
2 decode( grouping(job),1,'<===job'),
3 decode( grouping(deptno),1,'<===deptno')
4 from emp group by rollup(deptno,job)
say, if i don't want sub totals for job, i need totals only based on deptno. can't we do this without using having clause in 9i?
April 06, 2004 - 8:59 am UTC
1 select deptno, job, sum(sal), decode(grouping(job),1,'<<<==' ) grp
2 from emp
3* group by grouping sets((deptno,job),(deptno))
scott@ORA10G> /
DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <<<==
20 CLERK 1900
20 ANALYST 10781.24
20 MANAGER 2975
20 15656.24 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==
12 rows selected.
no subtotal for single row
John, April 22, 2004 - 1:22 am UTC
Tom,
Can we avoid displaying the subtotal for single rows without using having clause?
like for example, if you have deptno 40 job CLERK, the below query gives me the output:
1 select deptno, job, sum(sal), decode(grouping(job),1,'<<<==' ) grp
2 from emp
3* group by grouping sets((deptno,job),(deptno))
scott@ORA10G> /
DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <<<==
20 CLERK 1900
20 ANALYST 10781.24
20 MANAGER 2975
20 15656.24 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==
40 CLERK 3600
40 3600 <<<==
12 rows selected.
April 22, 2004 - 7:31 am UTC
ops$tkyte@ORA9IR2> select deptno, job, sum(sal), decode(grouping(job),1,'<<<==' ) grp
2 from emp
3 group by grouping sets((deptno,job),(deptno))<b>
4 having count(*) > 1 OR grouping(job) <> 1</b>
5 /
DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <<<==
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==
40 CLERK
A reader, August 27, 2004 - 11:29 pm UTC
A reader, August 19, 2005 - 2:13 pm UTC
Grand total
VA, December 20, 2005 - 11:04 pm UTC
But can this feature be used to stick a total on any column in an existing report?
Say I have a report on
select * from emp
I just want to add a row with the grand totals for the SAL and COMM columns. How can I do this?
Thanks
December 21, 2005 - 7:17 am UTC
group by rollup or group by grouping sets can do that if needed.
scott@ORA10GR2> select empno, ename, sum(sal), sum(comm) from emp group by grouping sets ((empno,ename),());
EMPNO ENAME SUM(SAL) SUM(COMM)
---------- ---------- ---------- ----------
7900 JAMES 950
7369 SMITH 800
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975
7654 MARTIN 1250 1400
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500 0
7876 ADAMS 1100
7902 FORD 3000
7934 MILLER 1300
29025 2200
15 rows selected.
Or ..
VA, December 21, 2005 - 10:26 am UTC
I came up with the following
select
decode(grouping_id(empno),1,'Grand Total',empno) empno,
ename,job,deptno,
sum(sal) sum_sal,
avg(comm) avg_comm,
max(sal) max_sal
from emp
group by rollup(empno,ename,job,deptno)
having grouping_id(empno,ename,job,deptno) in (0,15)
The GROUPING_ID gives a binary "bitmap" of which columns are showing a detail row vs. a aggregate/super-aggregate row. In this case, we want to show the lowest level of detail (0) and the highest grand total (15=binary 1111 or all bits turned on).
Yes, it is a little wasteful to generate intermediate aggregates and throw them away, but it works and it is fast!
Thanks
December 21, 2005 - 7:36 pm UTC
grouping sets only does the ones you need and () is pretty easy to type ;)
grouping sets
VA, December 21, 2005 - 8:38 pm UTC
Not sure I understand. How would my query above be re-written using grouping sets and produce identical results?
Thanks
A reader, December 21, 2005 - 8:44 pm UTC
I got it, sorry for not trying this before asking you.
select
decode(grouping_id(empno),1,'Grand Total',empno) empno,
ename,job,deptno,
sum(sal) sum_sal,
avg(comm) avg_comm,
max(sal) max_sal
from emp
group by grouping sets ( (empno,ename,job,deptno), () )
would produce the same result as
select
decode(grouping_id(empno),1,'Grand Total',empno) empno,
ename,job,deptno,
sum(sal) sum_sal,
avg(comm) avg_comm,
max(sal) max_sal
from emp
group by rollup(empno,ename,job,deptno)
having grouping_id(empno,ename,job,deptno) in (0,15)
and probably be a little more efficient.
Order of rows after rollup
A reader, May 06, 2006 - 6:10 am UTC
How can I get the following output, specially the
order it is in using the sample TIME DIMENSION.
Year Qtr Month Sales
1991 110,000
1991 Q1 5,000
1991 Q1 JAN 1,000
1991 Q1 FEB 1,000
.
.
.
1991 Q2 50,000
1991 Q2 JAN 20,000
.
.
.
1992 200000
1992 Q1 1000
.
.
ROLLUP puts the made up aggregated to row
at last, but I want it on top.
Thanks
May 07, 2006 - 11:22 am UTC
well, rollup lets you use grouping to see what dimensions have been aggregated over, so you can first query:
ops$tkyte@ORA10GR2> select deptno, job, ename, sum(sal) sum_sal,
2 grouping(deptno) gd, grouping(job) gj, grouping(ename) ge
3 from emp
4 group by rollup( deptno, job, ename );
DEPTNO JOB ENAME SUM_SAL GD GJ GE
---------- --------- ---------- ---------- ---------- ---------- ----------
10 CLERK MILLER 1300 0 0 0
10 CLERK 1300 0 0 1
10 MANAGER CLARK 2450 0 0 0
10 MANAGER 2450 0 0 1
10 PRESIDENT KING 5000 0 0 0
10 PRESIDENT 5000 0 0 1
10 8750 0 1 1
20 CLERK ADAMS 1100 0 0 0
20 CLERK SMITH 800 0 0 0
20 CLERK 1900 0 0 1
20 ANALYST FORD 3000 0 0 0
20 ANALYST SCOTT 3000 0 0 0
20 ANALYST 6000 0 0 1
20 MANAGER JONES 2975 0 0 0
20 MANAGER 2975 0 0 1
20 10875 0 1 1
30 CLERK JAMES 950 0 0 0
30 CLERK 950 0 0 1
30 MANAGER BLAKE 2850 0 0 0
30 MANAGER 2850 0 0 1
30 SALESMAN WARD 1250 0 0 0
30 SALESMAN ALLEN 1600 0 0 0
30 SALESMAN MARTIN 1250 0 0 0
30 SALESMAN TURNER 1500 0 0 0
30 SALESMAN 5600 0 0 1
30 9400 0 1 1
29025 1 1 1
27 rows selected.
<b>to get a feel for what it looks like - then, you can order by those columns - putting the 1's first in each group:</b>
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select deptno, job, ename, sum_sal, gd, gj, ge
2 from (
3 select deptno, job, ename, sum(sal) sum_sal,
4 grouping(deptno) gd, grouping(job) gj, grouping(ename) ge
5 from emp
6 group by rollup( deptno, job, ename )
7 )
8 order by gd DESC, deptno, gj DESC, job, ge DESC, ename
9 /
DEPTNO JOB ENAME SUM_SAL GD GJ GE
---------- --------- ---------- ---------- ---------- ---------- ----------
29025 1 1 1
10 8750 0 1 1
10 CLERK 1300 0 0 1
10 CLERK MILLER 1300 0 0 0
10 MANAGER 2450 0 0 1
10 MANAGER CLARK 2450 0 0 0
10 PRESIDENT 5000 0 0 1
10 PRESIDENT KING 5000 0 0 0
20 10875 0 1 1
20 ANALYST 6000 0 0 1
20 ANALYST FORD 3000 0 0 0
20 ANALYST SCOTT 3000 0 0 0
20 CLERK 1900 0 0 1
20 CLERK ADAMS 1100 0 0 0
20 CLERK SMITH 800 0 0 0
20 MANAGER 2975 0 0 1
20 MANAGER JONES 2975 0 0 0
30 9400 0 1 1
30 CLERK 950 0 0 1
30 CLERK JAMES 950 0 0 0
30 MANAGER 2850 0 0 1
30 MANAGER BLAKE 2850 0 0 0
30 SALESMAN 5600 0 0 1
30 SALESMAN ALLEN 1600 0 0 0
30 SALESMAN MARTIN 1250 0 0 0
30 SALESMAN TURNER 1500 0 0 0
30 SALESMAN WARD 1250 0 0 0
27 rows selected.
<b>Note: this is - the rollup is - one of the few times you can get ordered data without using an ORDER BY (cube being the other) in a realiable fashion, every other time, you have to use order by to get data ordered. group by and cube return data in a predicable "order", we can use order by to rearrange that as fits our needs</b>
Is inline view necessary?
A reader, May 08, 2006 - 1:57 am UTC
Thank you, thats a good solution.
1) Can we generalize this technique for concatenated and
composite group by
(e.g GROUP BY col1, ROLLUP(col2, (col3,col4))?
2) Is it necessary to use inline view? What will
be the difference between your query and this (output
wise and/or performance wise).
select deptno, job, ename, sum(sal) sum_sal,
grouping(deptno) gd, grouping(job) gj, grouping
(ename) ge
from emp
group by rollup( deptno, job, ename )
order by grouping(deptno) DESC, deptno, grouping(job) DESC,
job, grouping(ename) DESC, ename.
3) Page 18-22 SQL Referrence 9.2
"
The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause."
So I will assume this means if we only use GROUP BY alone,
the rows will be ORDERED if ROLLUP, CUBE or GROUPING SETS
or concatenated (e.g GROUP BY col1, ROLLUP(col2))is used, right? OR is it a documentation bug?.
May 08, 2006 - 8:12 am UTC
1) play with grouping_id/grouping functions - you can make it do whatever you want.
2) I like inline views for readability and incremental development. You can order by the functions - sure.
should be "the same" as view merging kicks in and Oracle sees them as the same query
3) this is group by ROLLUP, CUBE and GROUPING sets.
You are not just using a group by here - you are using a group by rollup. The group by extensions return the results in an order by the grouping functions (details then aggregates then aggregated aggregates...)
replacing group by with group by grouping sets
A reader, May 09, 2006 - 2:11 am UTC
Ok, so can I say that both of the following queries
are exactly equivialent.
1- SELECT ... FROM t1 GROUP BY col1,col2 ORDER BY col1,col2;
2- SELECT ... FROM t1 GROUP BY GROUPING SETS((col1,col2));
if yes, then this means every query that has
a GROUP BY only clause with ORDER BY can be converted
into GROUP BY GROUPING SETS without ORDER BY query.
right?
May 09, 2006 - 7:53 am UTC
I believe you can only say "the grouping sets will be returned - each in order"
so "no", i would not say that is "right"
In fact:
ops$tkyte@ORA10GR2> select ename, deptno, sum(sal),
2 grouping(ename) ge, grouping(deptno) gd
3 from emp
4 group by grouping sets((ename,deptno))
5 /
ENAME DEPTNO SUM(SAL) GE GD
---------- ---------- ---------- ---------- ----------
SMITH 20 800 0 0
JONES 20 2975 0 0
FORD 20 3000 0 0
CLARK 10 2450 0 0
SCOTT 20 3000 0 0
TURNER 30 1500 0 0
JAMES 30 950 0 0
KING 10 5000 0 0
ADAMS 20 1100 0 0
BLAKE 30 2850 0 0
MILLER 10 1300 0 0
ALLEN 30 1600 0 0
WARD 30 1250 0 0
MARTIN 30 1250 0 0
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 462 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 14 | 462 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 462 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
The groups are returned "predicably".
meaning of "predicable"
A reader, May 09, 2006 - 8:15 am UTC
What I understand from this is,
if I execute the query
SELECT job, ename, SUM(sal)
FROM emp
GROUP BY GROUPING SETS((job,ename));
the output could be
Job ename SAL
---- ----- ----
Z D 2
A E 3
Z A 4
which is not equivalent to output of
SELECT job, ename, SUM(sal)
FROM emp
GROUP BY job, ename ORDER BY job, ename.
right?
So this means that even when using ROLLUP, CUBE,or
GROUPING SETS, the ORDER of rows are not
completely guaranteed until we use ORDER BY,
we can only predict that the aggregated rows will appear in last but not guarantee
the ORDER of data, like job=Z can appear before job=A.
right?
May 09, 2006 - 8:30 am UTC
correct. as demonstrated right above.
group by grouping sets and average calculation
A reader, September 09, 2006 - 9:13 am UTC
I tried the group by grouping sets. In my scenario i wanted the grand totals of aggregate functions like count, sum, avg
. I get correct grand total values for aggregate functions like count, sum (ie grand total of count & sum values ) but in case of average the group by grouping sets does not give the value which is required (which is average of all averages).
To illustrate
Location AVG_OPEN
------------ -------------------
US 13
NON US 26
16
Its calculating the individual averages correctly like US - 13 and NON US - 26
but the running total for the average should have been (13+26)/2 = 19.5
but its giving me 16 which is nowhere nearby.
i am using the query something like this (not the exact query)
select id,name, sum(decode(ACTIVITY.STATUS,'Open',1,0)) ,
round(avg( decode(ACTIVITY.STATUS,'Open',ACTIVITY.STATUS_DIFF)),1)
from <table names> where <clauses>
group by grouping sets (id,name,())
In the above the sum aggregate gives me a correct grand total but the avg grand total gives a value which is not the average of averages calculated
September 09, 2006 - 12:23 pm UTC
well, it did the right "thing"
maybe you want to use the analytic function average - which can compute a running total sort of average.
but I don't really have an example to work with...
group by grouping sets & analytical functions
A reader, September 11, 2006 - 9:58 am UTC
Hi Tom
Yeah I got it. Thanks for the clarification.
So its not necessary that the average of all individual averages is the same as the grand total average as you mentioned & Oracle was giving the right average.
To give a view of the data I have which i used to calculate:
Days Count Avg
==================
50 5 10
10 1 10
5 1 5
2 2 1
So the group by grouping sets will give me a grand total average which will be something like this (67/9) = 7.44
where as what we need to show in the report is the running total for the average column (10+10+5+1) = 26/4 = 6.5.
Here is the query I have used :
*******************************************************
select DEALER.DEALER_CDE, DEALER.COMPANY_NAME,
sum(decode(ACTIVITY.STATUS,'New',1,0)) ,
sum(decode(ACTIVITY.STATUS,'Open',1,0)) ,
round(avg( decode (ACTIVITY.STATUS,'Open',ACTIVITY.STATUS_DIFF)),1) ,
sum(decode(ACTIVITY.STATUS,'Sent',1,0)) ,
round(avg( decode (ACTIVITY.status,'Sent',ACTIVITY.STATUS_DIFF)),1) ,
sum(decode(ACTIVITY.STATUS,'Dead',1,0)) ,
round(avg( decode (ACTIVITY.STATUS,'Dead',ACTIVITY.STATUS_DIFF)),1)
from ACTIVITY, REQUEST , DEALER
where REQUEST.DEALER_NUMBER = DEALER.DEALER_ID
and REQUEST.CONFIRMATION='Y' and REQUEST.REQUEST_ID = ACTIVITY.REQUEST_ID
and REQUEST.REQUEST_TYPE='T' and ACTIVITY.ACTIVITY_DATE
between to_date('2006-09-01','YYYY-MM-DD') and to_date('2006-09-09' ,'YYYY-MM-DD')
group by grouping sets((DEALER.DEALER_CDE, DEALER.COMPANY_NAME),
()) order by DEALER.COMPANY_NAME
*******************************************************
So if you can give me a hint how to go about applying the anlaytical function average in the above scenario that will be very helpful
Regards,
September 11, 2006 - 10:40 am UTC
need create table, insert into's to play with.
But presuming you have a query that returns that data set (sorted), you would:
ops$tkyte%ORA10GR2> select days, cnt, avrg,
2 sum(avrg) over (order by r) running_tot,
3 count(*) over (order by r) running_cnt,
4 sum(avrg) over (order by r) /
5 count(*) over (order by r) your_avg
6 from (
7 select rownum r, t.*
8 from (select * from t) t
9 )
10 /
DAYS CNT AVRG RUNNING_TOT RUNNING_CNT YOUR_AVG
---------- ---------- ---------- ----------- ----------- ----------
50 5 10 10 1 10
10 1 10 20 2 10
5 1 5 25 3 8.33333333
2 2 1 26 4 6.5
Your query is my select * from t on line 8 - it would have the ordering/whatever in it.
A similar Question
Mette, November 21, 2006 - 6:23 pm UTC
Hi Tom.
I have a query and I want to have the "grand total" last.
select count(*) Antal,
case when grouping_id(genrenavn) = 1 then '=== Totalt ===' when b.genrenavn is null then 'Unknown/Null' else b.genrenavn end genrenavn
from enhed a, genre b
where a.genre = b.genre (+)
group by cube(b.genrenavn)
order by 2
The result is
1617 === Totalt ===
4 Alternative
1 AlternRock
1 Ambient
etc
67 Unknown/Null
My preferred result would be:
4 Alternative
1 AlternRock
1 Ambient
etc
67 Unknown/Null
1617 Grand Total (or any other suitable text)
I can force it on top with some special chars (here ===), but how do I get it last (without adding too many strange chars)
regards
Mette
November 22, 2006 - 4:39 pm UTC
1 select grouping( deptno), deptno, count(*)
2 from emp
3* group by grouping sets( (deptno), () )
scott%ORA10GR2> /
GROUPING(DEPTNO) DEPTNO COUNT(*)
---------------- ---------- ----------
0 10 3
0 20 5
0 30 6
1 14
is this possible
A reader, November 23, 2006 - 1:34 am UTC
Tom your answers are really excellant...anyways..
my question is that without using
compute sum of sal,comm on deptno,job
i want a query to sum up sal and comm grouped by job and deptno and show all the columns...
below is my query
select EMPNO,ENAME,JOB,MGR,DEPTNO,sum(COMM) comm,sum(SAL) sal from emp group by rollup(DEPTNO,job)
November 24, 2006 - 6:15 pm UTC
so, did it work?
Thank you very much :-)
Mette, November 23, 2006 - 6:59 am UTC
Hi Tom,
I really appreciated you answer. I have tried to look into the Oracle SQL Reference for further info regarding grouping sets etc. Is there a better place?
And just a question:
When you dont have the order in the SQL statement in your answer to me, can I always be sure the data is sorted? (I know one normally ALWAYS have to order by to ensure a sorted output)?
Apart from the sort order what is the diff of
select grouping( deptno), deptno, count(*)
from emp
group by grouping sets( (deptno), () )
and
select grouping( deptno), deptno, count(*)
from emp
group by cube ((deptno))
?
regards
Mette
PS Pls. Write a book on rock'n'roll (aka Analytics)
November 24, 2006 - 6:22 pm UTC
The data warehousing guide believe it or not should be read by EVERYONE.
they talk about this stuff in greater detail (examples)
A special group by
A reader, November 23, 2006 - 10:15 am UTC
Dear Tom,
I would like to know if you can help me to solve the following situation.
create table t4 (x number, dat DATE, usr VARCHAR2(10))
insert into t4 values (110, sysdate, 'You');
insert into t4 values (110, sysdate, 'Me');
insert into t4 values (110, sysdate, 'Me');
insert into t4 values (110, sysdate, 'You');
select x, dat, usr from t4
group by x,dat, usr
X DAT USR
--------- ------------------- ----------
110 23/11/2006 15:02:24 You
110 23/11/2006 15:02:26 Me
110 23/11/2006 15:02:27 Me
110 23/11/2006 15:02:32 You
I would like to consider that 23/11/2006 15:02:26 = 23/11/2006 15:02:27 and would like to get the following results for the same select
select x, dat, usr from t4
group by x,dat, usr
X DAT USR
--------- ------------------- ----------
110 23/11/2006 15:02:24 You
110 23/11/2006 15:02:26 Me
110 23/11/2006 15:02:32 You
In fact I would like to ignore the 1 sec difference.
Is this possible
Thanks very much for your answer
Best Regards
November 24, 2006 - 6:23 pm UTC
umm, that rule you just suggested is very ambigous, care to "explain" it???
what makes two things IN GENERAL equal?
Eliminate Redundant Column Values
A reader, March 08, 2007 - 4:01 pm UTC
Is there a way to format results like the following without knowing how many deptno's?
DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750 <<<==
20 CLERK 1900
ANALYST 10781.24
MANAGER 2975
15656.24 <<<==
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400 <<<==
March 08, 2007 - 8:37 pm UTC
scott%ORA9IR2> select deptno, job, sum(sal) from emp group by grouping sets( (deptno,job), (deptno) );
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
12 rows selected.
Conditional grouping
Jay, March 09, 2007 - 9:58 am UTC
Good Morning Tom!
Say we have a query like this¿
select deptno, job, sum(sal), decode(grouping(job),1,'<<<==' ) grp
from emp
group by grouping sets((deptno,job),(deptno))
scott@ORA10G> /
DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <<<==
20 CLERK 1900
20 ANALYST 10781.24
20 MANAGER 2975
20 15656.24 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==
I would like to see the details ONLY when a certain criteria is met.
For example, I would to see the results by detail only if the total salary by department is greater than $ 9000, else, I would want to see only the total.
The result set should be something like this:
DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 8750 <<<==
20 CLERK 1900
20 ANALYST 10781.24
20 MANAGER 2975
20 15656.24 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==
Would this be something possible to do Tom? Thanks a lot for your time.
b) Tom, can you suggest me some books that I can buy and read to master this language please? I want something that will take me all the way from learning the basics to mastering the language?
Thanks a lot and have a wonderful day!
March 09, 2007 - 11:20 am UTC
a)
scott%ORA9IR2> select deptno, job, sum(sal), sumsal,
2 decode( grouping(job), 1, '<==' ) grp
3 from (
4 select deptno, job, sal, sum(sal) over (partition by deptno) sumsal
5 from emp
6 )
7 group by grouping sets((deptno,job,sumsal),(deptno))
8 having sumsal > 9000 or grouping(job) = 1
9 /
DEPTNO JOB SUM(SAL) SUMSAL GRP
---------- --------- ---------- ---------- ---
10 8750 <==
20 CLERK 1900 10875
20 ANALYST 6000 10875
20 MANAGER 2975 10875
20 10875 <==
30 CLERK 950 9400
30 MANAGER 2850 9400
30 SALESMAN 5600 9400
30 9400 <==
9 rows selected.
b)
sql reference manual
data warehousing guide
EXPERIENCE, PRACTICE
Thanks!
Jay, March 09, 2007 - 11:34 am UTC
Thank you Sir!
Could you please tell me when it would be a good time for me to read your 'expert 1 on 1' book for 9i and up? I am going to buy this one but I just wanted to know if the book is for only the people who have already very good knowledge of pl/sql or for anyone?
Thanks again for your time. I really appreciate it a lot. Thanks!
March 12, 2007 - 2:49 pm UTC
it is for everyone - newbies, experienced. It presumes no prior knowledge really of PLSQL and will fill in lots of gaps people with many years of Oracle experience have as well as get someone just starting out well versed in many of the concepts.
Thank you!
Jay, March 12, 2007 - 4:32 pm UTC
Thank you very much Tom!
Rollup with ordered detail rows
tkyte_fan, October 15, 2008 - 6:11 pm UTC
Hi Tom, I have been looking for a way to sort the detail rows within a rollup query.
As you mentioned that group by extensions gives ordered results, but only the aggregates are ordered. How can I get the sorting for leaf data (i.e. detail rows) without impacting the aggregate ordering?
To put in other words, I was looking for something like:
select deptno, job, sum(sal),
decode( grouping(job),1,'<===job'),
decode( grouping(deptno),1,'<===deptno')
from emp group by rollup(job,deptno)
/
DEPTNO JOB SUM(SAL) DECODE( DECODE(GRO
---------- --------- ---------- ------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <===job
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875 <===job
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <===job
29025 <===job <===deptno
If there is a way to ensure that "JOB" column is always in ascending order, and similarly for more than one columns, but also that rollup aggregates appear after/before each group.
Appreciate your help in finding some answer to this. I have searched for this in so many places, including the Data Warehousing guide, but no luck yet.
Thanks.
October 17, 2008 - 8:28 pm UTC
you have your rollup backwards there I believe....
you are rolling up over job and then over deptno, but your output is by deptno and then job....
in any event, you have access to the grouping column, it is sortable.
ops$tkyte%ORA10GR2> select deptno, job, sum(sal),
2 grouping(job), grouping(deptno)
3 from scott.emp group by rollup(deptno,job)
4 order by deptno, grouping(job), job, grouping(deptno)
5 /
DEPTNO JOB SUM(SAL) GROUPING(JOB) GROUPING(DEPTNO)
---------- --------- ---------- ------------- ----------------
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
10 8750 1 0
20 ANALYST 95738.25 0 0
20 CLERK 1900 0 0
20 MANAGER 2975 0 0
20 100613.25 1 0
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 5600 0 0
30 9400 1 0
118763.25 1 1
13 rows selected.
Just...
A reader, October 16, 2008 - 5:23 pm UTC
select deptno, job, sum(sal),
decode( grouping(job),1,'<===job'),
decode( grouping(deptno),1,'<===deptno')
from emp
group by rollup(job,deptno)
order by deptno, job
/
That doesn't work?
October 17, 2008 - 9:16 pm UTC
It does not if job is allowed to be NULL in general, no.
Rollup with ordered detail rows
tkyte_fan, October 16, 2008 - 8:15 pm UTC
That doesn't work well. It will cause the details rows and summary rows to move away from each other, if sorting is applied to the entire result set. This example may not demonstrate that, but imagine if dept 20 has a job by name "AAAClerk" (for example).
If that happens then there is no way for me to find out where that "AAAClerk" row belongs (which summary row).
I appreciate your response though. Tom it will be very helpful if you can assist here ? I have been trying hard to find the solution so far, but no luck yet.
Dan, October 17, 2008 - 12:08 pm UTC
If you order by deptno first, then by job, how would the details separate from the aggregate? Ordering by deptno will make all rows for the same deptno end up next to eachother, and then order by job inside of that.
You would get:
DEPTNO JOB SUM(SAL) DECODE( DECODE(GRO
---------- --------- ---------- ------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <===job
20 AAACLERK 1000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 11875 <===job
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <===job
30025 <===job <===deptno
October 18, 2008 - 9:42 pm UTC
4 order by deptno, grouping(job), job, grouping(deptno)
order by deptno (nulls go last by default)....
within each deptno, order the rows by whether job is grouped over or not (details come first). Within the details for JOB - sort them...
for the last deptno (the set of NULL deptnos) put the aggregate deptno last
grouping(deptno) could come first now that I look at this again as well. sort by whether deptno is grouped over or not - then within the deptno details by deptno and so on...
what about subquery factoring?
Radoslav Golian, October 19, 2008 - 1:10 pm UTC
SQL> create table tap_rtx_009(
vplcode number,
rated_flat_amount number,
start_d_t date,
actual_volume number
);
Table created.
SQL>insert into tap_rtx_009
select mod(trunc(dbms_random.value(1,1000)), 7),
trunc(dbms_random.value(1,10)),
sysdate + trunc(dbms_random.value(-10,10)),
trunc(dbms_random.value(1,20))
from dual
connect by level < 1001;
1000 rows created.
SQL> commit;
Commit complete.
SQL>SELECT vplcode, SUM(rated_flat_amount)/COUNT(*) amt
FROM tap_rtx_009
WHERE vplcode = 2
GROUP BY vplcode, start_d_t, actual_volume,
rated_flat_amount
HAVING COUNT(*) > 1
VPLCODE AMT
---------- ----------
2 2
2 9
2 5
SQL>WITH main_select AS (
SELECT vplcode, SUM(rated_flat_amount)/COUNT(*) amt
FROM tap_rtx_009
WHERE vplcode = 2
GROUP BY vplcode, start_d_t, actual_volume,
rated_flat_amount
HAVING COUNT(*) > 1)
SELECT vplcode, amt FROM (
SELECT 1, vplcode, amt
FROM main_select
UNION ALL
SELECT 2, NULL, sum(amt)
FROM main_select
ORDER BY 1)
VPLCODE AMT
---------- ----------
2 2
2 9
2 5
16
SQL> drop table tap_rtx_009;
Table dropped.
October 21, 2008 - 11:40 am UTC
um, what about it exactly - not sure what you are addressing here or if you are asking a new question or what.
but no, i would not use a union all query of a with subquery to get what is easily retrieved with a group by rollup. Doesn't seem to make sense to do it that way. Not only is it harder to code - but I see multiple passes of the same data there.
A reader, November 22, 2013 - 9:07 am UTC