Rollup and Cube
Reddy, March 30, 2002 - 12:11 pm UTC
This has been almost solved my problem.
Thank u very much
Reddy
Just the Grand Total please...
Robert, December 31, 2002 - 4:29 pm UTC
Tom, ROLLUP is give me more info than I need...
I just want the grand total number
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select job, deptno, count(*) from emp group by job, deptno ;
JOB DEPTNO COUNT(*)
--------- ------ ----------
ANALYST 20 2
CLERK 10 1
CLERK 20 2
CLERK 30 1
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
PRESIDENT 10 1
SALESMAN 30 4
Is there a way to get ROLLUP (or analytics) to output the JUST grand total rollup count like below ? without going to the inline view but just manipulate the GROUP BY ... ROLLUP (...) ?
JOB DEPTNO COUNT(*)
--------- ------ ----------
ANALYST 20 2
CLERK 10 1
CLERK 20 2
CLERK 30 1
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
PRESIDENT 10 1
SALESMAN 30 4
14
Thanks and have a fabulous New Year !
December 31, 2002 - 4:49 pm UTC
This'll work in 8i:
scott@ORA920> select job, deptno, count(*)
2 from emp
3 group by rollup( job, deptno )
4 having (grouping(job) = grouping(deptno))
5 /
JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 10 1
CLERK 20 2
CLERK 30 1
ANALYST 20 2
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
SALESMAN 30 4
PRESIDENT 10 1
14
10 rows selected.
and in 9i, recognizing the need for this, they added grouping sets:
scott@ORA920> select job, deptno, count(*)
2 from emp
3 group by grouping sets ( (job,deptno), () )
4 /
JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 10 1
CLERK 20 2
CLERK 30 1
ANALYST 20 2
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
SALESMAN 30 4
PRESIDENT 10 1
14
10 rows selected.
conditional rollup
A reader, May 06, 2003 - 5:58 pm UTC
Tom,
Can we have conditional rollup say if its deptno 20 then
do not rollup by job else for the rest of the departments
rollup by job....
Thanks,
May 06, 2003 - 6:04 pm UTC
not really.
obviously -- it would be easy to add a where or having clause to eliminate the unwanted rows.
Having clause neglected
A reader, July 21, 2003 - 12:37 am UTC
the comment point above
select job, deptno, count(*)
2 from emp
3 group by grouping sets ( (job,deptno), () )
having count(*) > 1
Gives wrong result it never considers the having clause.
Why
Same for 8i soln also
If we include having count(*) > 1 it is not considered
July 21, 2003 - 8:14 am UTC
what do you mean?
scott@ORA920LAP> select job, deptno, count(*)
2 from emp
3 group by grouping sets ( (job,deptno), () )
4 having count(*) > 1
5 /
JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 20 2
ANALYST 20 2
SALESMAN 30 4
14
scott@ORA920LAP> select job, deptno, count(*)
2 from emp
3 group by grouping sets ( (job,deptno), () )
4 /
JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 10 1
CLERK 20 2
CLERK 30 1
ANALYST 20 2
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
SALESMAN 30 4
PRESIDENT 10 1
14
10 rows selected.
Total must be 8 and not 14 hence i said having is bypassed
A reader, July 21, 2003 - 9:36 am UTC
As you see the total count(*) is wrong.
Shouldnt the Total count(*) be 8 instead of 14
scott@ORA920LAP> select job, deptno, count(*)
2 from emp
3 group by grouping sets ( (job,deptno), () )
4 having count(*) > 1
5 /
JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 20 2
ANALYST 20 2
SALESMAN 30 4
14
July 21, 2003 - 3:26 pm UTC
umm, there are 14 rows?
scott@ORA920LAP> select job, deptno, count(*),
2 grouping(job), grouping(deptno)
3 from emp
4 group by grouping sets ( (job,deptno), () )
5 having count(*) > 1
6 /
JOB DEPTNO COUNT(*) GROUPING(JOB) GROUPING(DEPTNO)
--------- ---------- ---------- ------------- ----------------
CLERK 20 2 0 0
ANALYST 20 2 0 0
SALESMAN 30 4 0 0
14 1 1
scott@ORA920LAP> select count(*)
2 from emp
3 group by grouping sets ( () )
4 having count(*) > 1
5 /
COUNT(*)
----------
14
This definitely deserves 0 stars
A reader, July 21, 2003 - 5:42 pm UTC
I think ur not TOM may be he is on vacation
I want to have the grand total of the existing rows which have count(*) > 1 and that is 8 and not 14.
Can u pls if possible give some soln.
So I need
JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 20 2
ANALYST 20 2
SALESMAN 30 4
8 ( and not 14).
Tom where are you ?
July 22, 2003 - 8:10 am UTC
No, this is me.
you know what -- when you say what you want, one can give that to you.
when you say things like "hey, this isn't returning the right stuff" (how very clear of you -- thanks) and I answer "hey, it is returning exactly what you asked for" -- what do you expect.
You did the query entirely wrong.
scott@ORA920LAP> select job, deptno, sum(cnt) cnt
2 from (
3 select job, deptno, count(*) cnt
4 from emp
5 group by job, deptno
6 having count(*) > 1
7 )
8 group by grouping sets( (job,deptno),() )
9 /
JOB DEPTNO CNT
--------- ---------- ----------
CLERK 20 2
ANALYST 20 2
SALESMAN 30 4
8
think about it -- you wanted the sum of the counts after the having clause was processed.
Having clause
c.a., July 22, 2003 - 1:59 am UTC
Actually, HAVING clause is only for display. Basically, you are saying 'do not display where count(*) < 2' with the statement below. Happens after the result set.
scott@ORA920LAP> select job, deptno, count(*)
2 from emp
3 group by grouping sets ( (job,deptno), () )
4 having count(*) > 1
5 /
you should put the conditions into the where clause! You can do this a few different ways. One as below.
SELECT job,deptno,sum(cnt)
FROM (Select job, deptno, count(*) cnt
From emp
Group By job, deptno)
WHERE cnt > 1
GROUP BY grouping sets ((job,deptno),(' '))
/
JOB DEPTNO SUM(CNT)
ANALYST 20 2
CLERK 20 2
SALESMAN 30 4
8
Thanx much Tom and the person from Vancouver
A reader, July 22, 2003 - 9:39 am UTC
The combined comments explains it all I didnt know having is just for display.
I think it is done in the very end after grouping and all is done.
Q on Cube
Anil Pant, October 16, 2003 - 2:18 am UTC
I've done the following :
create table akp1 as select object_type otype, object_id oid, to_char(CREATED,'month') mth
from user_objects
where object_type in ('PACKAGE', 'TYPE', 'VIEW');
SQL> select otype, mth, count(*)
2 from akp1
3 group by cube(otype, mth);
Query 1 :
OTYPE MTH COUNT(*)
------------------ --------- ----------
118
august 3
july 109
october 3
september 3
TYPE 2
TYPE october 2
VIEW 15
VIEW august 1
VIEW july 13
VIEW october 1
OTYPE MTH COUNT(*)
------------------ --------- ----------
PACKAGE 101
PACKAGE august 2
PACKAGE july 96
PACKAGE september 3
15 rows selected.
Query 2 :
SQL> select mth, otype, count(*)
2 from akp1
3 group by cube(mth, otype);
MTH OTYPE COUNT(*)
--------- ------------------ ----------
118
TYPE 2
VIEW 15
PACKAGE 101
august 3
august VIEW 1
august PACKAGE 2
july 109
july VIEW 13
july PACKAGE 96
october 3
MTH OTYPE COUNT(*)
--------- ------------------ ----------
october TYPE 2
october VIEW 1
september 3
september PACKAGE 3
15 rows selected.
My Q is why is the blank rows on the top.
MTH OTYPE COUNT(*)
--------- ------------------ ----------
118
Query 3 : If I give the same query with Order By clause then it's ok.
SQL> select mth, otype, count(*)
2 from akp1
3 group by cube(mth, otype)
4 order by mth, otype;
MTH OTYPE COUNT(*)
--------- ------------------ ----------
august PACKAGE 2
august VIEW 1
august 3
july PACKAGE 96
july VIEW 13
july 109
october TYPE 2
october VIEW 1
october 3
september PACKAGE 3
september 3
MTH OTYPE COUNT(*)
--------- ------------------ ----------
PACKAGE 101
TYPE 2
VIEW 15
118
But in none of the books this is given or discussed. Am I missed something ?
October 16, 2003 - 10:30 am UTC
group by cube just gives you every aggregation -- you use the GROUPING function to see what level of aggregation. it is free to give it to you as it "likes"
add columns grouping(mth), grouping(otype) to the select list and see what you can make of that.
How to subtotal this query
aish, October 22, 2003 - 11:13 am UTC
Select Distinct MGMT_COMPANY,
CUSTOMER_NAME,
TO_CHAR(SO_ORIGINAL_ORDER_DATE, 'MONTH') "MON",
NVL(Sum((SO_QUANTITY - NVL(SO_CANCEL_QTY, 0)) *
(SO_SALES_PRICE)),
0) "PRICE1",
NVL(Sum((PO_QTY - NVL(PO_CANCEL_QTY, 0)) *
PO_COST_TO_SITESTUFF),
0) "PRICE2"
From SALES AA
Where VENDOR_ID = 24 And TO_CHAR(SO_ORIGINAL_ORDER_DATE, 'YYYY') = '2003'
Group By Rollup(MGMT_COMPANY,
CUSTOMER_NAME,
TO_CHAR(SO_ORIGINAL_ORDER_DATE, 'MONTH'));
my out put is like this
MGMT_COMPANY CUSTOMER_NAME MON PRICE1 PRICE2
aa a jan 100 200
aa b feb 100 200
aa b mar 100 200
aa c jan 100 200
----- i need one subtotal here
bb a oct 100 200
----- i need one subtotal here
cc a jan 100 200
cc b feb 100 200
----- i need one subtotal here
----grand total
Iam using oracle 8i trying to get subtotal using your technique of using rownum inbetween .
but i couldn't.
Thanks in advance
aish
October 22, 2003 - 6:12 pm UTC
one subtotal of what? the rollup gives you the subtotals automatically.
ops$tkyte@ORA920> select decode( grouping( ename ), 1, 'Sub' ),
2 grouping( deptno ) gdeptno,
3 grouping( ename ) gename,
4 grouping( to_char(hiredate,'Mon') ) gdate,
5 deptno, ename, to_char(hiredate,'Mon') Mon, sum(sal)
6 from emp
7 group by rollup( deptno, ename, to_char(hiredate,'Mon') )
8 /
DEC GDEPTNO GENAME GDATE DEPTNO ENAME MON SUM(SAL)
--- ---------- ---------- ---------- ---------- ---------- --- ----------
0 0 0 10 KING Nov 5000
0 0 1 10 KING 5000
0 0 0 10 CLARK Jun 2450
0 0 1 10 CLARK 2450
0 0 0 10 MILLER Jan 1300
0 0 1 10 MILLER 1300
Sub 0 1 1 10 8750
0 0 0 20 FORD Dec 3000
0 0 1 20 FORD 3000
0 0 0 20 ADAMS Jan 1100
0 0 1 20 ADAMS 1100
0 0 0 20 JONES Apr 2975
0 0 1 20 JONES 2975
0 0 0 20 SCOTT Dec 3000
0 0 1 20 SCOTT 3000
0 0 0 20 SMITH Dec 800
0 0 1 20 SMITH 800
Sub 0 1 1 20 10875
0 0 0 30 WARD Feb 1250
0 0 1 30 WARD 1250
0 0 0 30 ALLEN Feb 1600
0 0 1 30 ALLEN 1600
0 0 0 30 BLAKE May 2850
0 0 1 30 BLAKE 2850
0 0 0 30 JAMES Dec 950
0 0 1 30 JAMES 950
0 0 0 30 MARTIN Sep 1250
0 0 1 30 MARTIN 1250
0 0 0 30 TURNER Sep 1500
0 0 1 30 TURNER 1500
Sub 0 1 1 30 9400
Sub 1 1 1 29025
32 rows selected.
and if you feel some records are "duplicated", you can use a having clause to remove them:
ops$tkyte@ORA920> select decode( grouping( ename ), 1, 'Sub' ),
2 grouping( deptno ) gdeptno,
3 grouping( ename ) gename,
4 grouping( to_char(hiredate,'Mon') ) gdate,
5 deptno, ename, to_char(hiredate,'Mon') Mon, sum(sal)
6 from emp
7 group by rollup( deptno, ename, to_char(hiredate,'Mon') )
8 having NOT( grouping(deptno) = 0 and grouping(ename) = 0 and grouping(to_char(hiredate,'Mon')) = 1 )
9 /
DEC GDEPTNO GENAME GDATE DEPTNO ENAME MON SUM(SAL)
--- ---------- ---------- ---------- ---------- ---------- --- ----------
0 0 0 10 KING Nov 5000
0 0 0 10 CLARK Jun 2450
0 0 0 10 MILLER Jan 1300
Sub 0 1 1 10 8750
0 0 0 20 FORD Dec 3000
0 0 0 20 ADAMS Jan 1100
0 0 0 20 JONES Apr 2975
0 0 0 20 SCOTT Dec 3000
0 0 0 20 SMITH Dec 800
Sub 0 1 1 20 10875
0 0 0 30 WARD Feb 1250
0 0 0 30 ALLEN Feb 1600
0 0 0 30 BLAKE May 2850
0 0 0 30 JAMES Dec 950
0 0 0 30 MARTIN Sep 1250
0 0 0 30 TURNER Sep 1500
Sub 0 1 1 30 9400
Sub 1 1 1 29025
18 rows selected.
Adding another column to the rollup.
ht, October 25, 2003 - 7:04 pm UTC
Tom,
Your examples are great. Is it possible to add a first_name column to this query? I tweaked the query but wasn't able to get it to work without duplicate lines unless I appended first_name||ename throughout the query.
This works fine for my purposes but it would be nice if I could have distinct columns instead of a concatenated one. This would let me sort on each column in my PSP.
Thanks,
ht
Got the answer in your forum.
ht, October 26, 2003 - 1:18 am UTC
Hi Tom,
Please disregard my ignorant question. I found the answer in your forum and it involves my misunderstanding of the "having" clause.
ht
break up
umesh kasturi, November 11, 2003 - 6:25 am UTC
Tom
I have some requirement like this
deptno name tot_cnt
10 'total_in_deptno_10' 4
'whose sal>4000' 2
'whose sal <4000' 1
'whose sal=4000' 1
.... The same repeats for deptno=20 ,30 and so on
I do not know how do i go about with analytic functions here
November 11, 2003 - 7:40 am UTC
we can do it in COLUMNS
scott@ORA920> select deptno, count(*) tot_dept, count(case when sal>4000 then 1 else null end) "sal>4000",
2 count(case when sal <4000 then 1 else null end) "sal<4000",
3 count(case when sal=4000 then 1 else null end) "sal=4000"
4 from emp
5 group by deptno;
DEPTNO TOT_DEPT sal>4000 sal<4000 sal=4000
---------- ---------- ---------- ---------- ----------
10 3 1 2 0
20 5 0 5 0
30 6 0 6 0
scott@ORA920>
don't even need analytics. We can unpivot that if you really need:
1 select deptno, decode( x, 1, 'total in deptno ' || deptno,
2 2, 'whose sal >4000',
3 3, 'whose sal <4000',
4 4, 'whose sal=4000' ) name,
5 decode( x, 1, tot_dept,
6 2, "sal>4000",
7 3, "sal<4000",
8 4, "sal=4000" ) tot_cnt
9 from ( select rownum x from all_objects where rownum <= 4),
10 ( select deptno,
11 count(*) tot_dept,
12 count(case when sal>4000 then 1 else null end) "sal>4000",
13 count(case when sal <4000 then 1 else null end) "sal<4000",
14 count(case when sal=4000 then 1 else null end) "sal=4000"
15 from emp
16 group by deptno
17 )
18* order by deptno, x
scott@ORA920> /
DEPTNO NAME TOT_CNT
---------- ------------------------------ ----------
10 total in deptno 10 3
10 whose sal >4000 1
10 whose sal <4000 2
10 whose sal=4000 0
20 total in deptno 20 5
20 whose sal >4000 0
20 whose sal <4000 5
20 whose sal=4000 0
30 total in deptno 30 6
30 whose sal >4000 0
30 whose sal <4000 6
30 whose sal=4000 0
12 rows selected.
grouping sets or rollup
igor, November 11, 2003 - 11:08 am UTC
Tom, you wrote absolutely correctly:
and in 9i, recognizing the need for this, they added grouping sets:
scott@ORA920> select job, deptno, count(*)
2 from emp
3 group by grouping sets ( (job,deptno), () )
4 /
JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 10 1
CLERK 20 2
CLERK 30 1
ANALYST 20 2
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
SALESMAN 30 4
PRESIDENT 10 1
14
10 rows selected.
I just want to add that grouping sets are not needed for this particular case - in 9i rollup still does this same job:
SQL> select job, deptno, count(*)
2 from emp
3 group by rollup( (job, deptno) );
JOB DEPTNO COUNT(*)
--------- ---------- ----------
CLERK 10 1
CLERK 20 2
CLERK 30 1
ANALYST 20 2
MANAGER 10 1
MANAGER 20 1
MANAGER 30 1
SALESMAN 30 4
PRESIDENT 10 1
14
10 rows selected.
how do this 8.1.7.4
umesh, November 24, 2003 - 2:50 am UTC
SQL> select * from
2 ( select deptno, sum(sal) from emp where deptno=30 group by deptno),
3 ( select deptno, sum(sal) from emp where deptno=20 group by deptno);
DEPTNO SUM(SAL) DEPTNO SUM(SAL)
---------- ---------- ---------- ----------
30 9400 20 10875
gives me values as expected in unpivoting
1 select * from
2 ( select deptno, sum(sal) from emp where deptno=40 group by deptno),
3* ( select deptno, sum(sal) from emp where deptno=20 group by deptno)
SQL> /
no rows selected
But this gives no rows as one of the queries return no rows
Tom Is there a way to overcome the limitation
November 24, 2003 - 7:58 am UTC
well -- there are no "deptno=40" rows!
but anyway, you are doing it "wrong" IMO
ops$tkyte@ORA920PC> select max( decode( deptno, 20, deptno ) ) deptno,
2 max( decode( deptno, 20, sum_sal ) ) sal,
3 max( decode( deptno, 30, deptno ) ) deptno,
4 max( decode( deptno, 30, sum_sal ) ) sal
5 from (
6 select deptno, sum(sal) sum_sal
7 from emp
8 where deptno in ( 30, 20 )
9 group by deptno
10 )
11 /
DEPTNO SAL DEPTNO SAL
---------- ---------- ---------- ----------
20 10875 30 9400
ops$tkyte@ORA920PC> select max( decode( deptno, 20, deptno ) ) deptno,
2 max( decode( deptno, 20, sum_sal ) ) sal,
3 max( decode( deptno, 30, deptno ) ) deptno,
4 max( decode( deptno, 30, sum_sal ) ) sal
5 from (
6 select deptno, sum(sal) sum_sal
7 from emp
8 where deptno in ( 40, 20 )
9 group by deptno
10 )
11 /
DEPTNO SAL DEPTNO SAL
---------- ---------- ---------- ----------
20 10875
ops$tkyte@ORA920PC>
Followup Decode Question
John, December 08, 2003 - 5:50 pm UTC
Tom,
Perhaps you can follow up this post with a specific decode question. I have a table with two specific columns that I need to decode on. If both columns are 0's, then I know it is one type of object, if the first is a 0 and the second another number I know it is another type of object, if the first is a 1 and the second another number I know it is a third type of object. For example:
TableA
col1 = itemtype
col2 = parentobjid
The allowable values for itemtype are 0 and 1 only.
The allowable values for parentobjid are 0 or greater.
So, if the combination is:
0, 0 = Object Type 1
0, x = Object Type 2
1, x = Object Type 3
Where x is any number greater than 0.
How would I write a decode statement for this? There are quite a number of other columns in this table, but I'm needing to differentiate a resultset based on the combination of values in these two columns.
Thanks!
December 09, 2003 - 6:01 am UTC
well, case would probably be easier:
case when c1 = 0
then case when c2 = 0
then 1
else 2
end
else 3
end
decode can work
decode( c1, 0, decode( c2, 0, 1, 2 ), 3 )
as well
How to get a Sum of Sum
Mir, March 10, 2004 - 5:49 pm UTC
Tom,
I want to publish percentage of a given set of values
Example:
DEPT SUM_EMP ALL_SUM PERCENTAGE
------- -------- ------- -----------------
10 5 22 22.72% (5*100)/22
20 10 22 45.45% (10*100/22
30 7 22 31.81% (7*100)/22
How can I achieve the above result assuming I have the following data in a table?
DEPT SUM_EMP
------ -------
10 5
20 10
30 7
Thanks in advance for your valuable input
Mir
March 10, 2004 - 6:40 pm UTC
scott@ORA9IR2> select deptno, sum_emp,
2 round( 100*ratio_to_report( sum_emp ) over (),2) rtr
3 from (
4 select deptno, count(*) sum_emp
5 from emp
6 group by deptno
7 )
8 /
DEPTNO SUM_EMP RTR
---------- ---------- ----------
10 3 21.43
20 5 35.71
30 6 42.86
the inline view is "your table"....
ratio_to_report?
SD, March 11, 2004 - 9:50 am UTC
Could you please explain ratio_to_report ?
March 11, 2004 - 1:54 pm UTC
it is an analytic function -- if you don't know what they are read either
a) my chapter on them in expert one on one Oracle
b) the chapter on them in the data warehousing guide (on otn.oracle.com)
they are extremely powerful, there are lots of them, they are the cooolest thing to happen to sql since the keyword "select"
it computed the ratio of SUM_EMP with regards to the "report" value (the sum of the values for the report).
it can be partitioned, windowed as well.
Nice
Maer, March 19, 2004 - 4:57 am UTC
Dear Tom,
I'm little confused with grouping function.Please see the query below.
SQL> select grouping(deptno) from emp group by rollup(deptno);
GROUPING(DEPTNO)
----------------
0
0 <<-- Why is this 0,0,0,1 coming up
0
1
SQL> select grouping(deptno) from emp group by cube(deptno);
GROUPING(DEPTNO)
----------------
1
0 <<-- Why it comes as 1,0,0,0
0
0
Could you please explain?
Please do reply.
Bye!
March 19, 2004 - 9:22 am UTC
since it is rather uninteresting to look at a single column, we'll expand it. but basically:
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/aggreg.htm#11441 http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/aggreg.htm#25172
rollup is like a "report", it rolls up the data as it moves along. you saw 0, 0, 0, 1 because you saw "detail, detail, detail, aggregation(detail)". Cube on the other hand, makes no such "flowing report" (it cannot, there is no logical progression, it is creating every combination of the group by sets). It progresses value by value and returns data "by group by columns". I'll start with your simple example (but expanded row wise to make a point):
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA9IR2> insert into emp (ename,sal,empno) values ( 'bob', 1000, 1234 );
1 row created.
ops$tkyte@ORA9IR2> insert into emp (ename,sal,empno) values ( 'mary', 2000, 1235 );
1 row created.
ops$tkyte@ORA9IR2> commit;
Commit complete.
<b>Notice -- i stuffed some NULLS in there on you.... it'll materially change the outcome:</b>
ops$tkyte@ORA9IR2> select deptno, grouping(deptno), sum(sal)
2 from emp
3 group by rollup(deptno)
4 /
DEPTNO GROUPING(DEPTNO) SUM(SAL)
---------- ---------------- ----------
10 0 8750
20 0 10875
30 0 9400
0 3000
1 32025
<b>Here it looks "flowing" if you will -- DEPTNO aggregates appear for 10, 20, 30 and then NULL (this shows why grouping(column) even exists -- so you can differentiate the last row from the next to last row!). After we give you the aggregates by deptno, you get the aggregates OVER deptno (makes sense, we can start feeding data to you before we get the final answer ourself -- the report "builds" from top to bottom)
Now, cube:</b>
ops$tkyte@ORA9IR2> select deptno, grouping(deptno), sum(sal)
2 from emp
3 group by cube(deptno)
4 /
DEPTNO GROUPING(DEPTNO) SUM(SAL)
---------- ---------------- ----------
0 3000
1 32025
10 0 8750
20 0 10875
30 0 9400
ops$tkyte@ORA9IR2>
<b>See, it is not 1,0,0,0,0 but rather -- DEPTNO "null" is presented (and in your example "null" was only the cubed result). Here the first return is the aggregate for deptno "null" and then the CUBED (summed over all deptnos) result after squashing the deptno dimension out of existence. Then we have the deptno "10" results and so on.
To make it more interesting -- lets add another column:</b>
ops$tkyte@ORA9IR2> select deptno, job, grouping(deptno), grouping(job), sum(sal)
2 from emp
3 group by rollup(deptno,job)
4 /
<b>Here we'll report by DEPTNO, JOB:</b>
DEPTNO JOB GROUPING(DEPTNO) GROUPING(JOB) SUM(SAL)
---------- --------- ---------------- ------------- ----------
0 0 3000
0 1 3000
<b>thats a little boring -- but only because there was one job "NULL" in that DEPTNO "NULL", gets more interesting as we move along:</b>
10 CLERK 0 0 1300
10 MANAGER 0 0 2450
10 PRESIDENT 0 0 5000
10 0 1 8750
<b>see, since job was second, we are basically getting data by deptno, job and then a summary by deptno - for each deptno:</b>
20 CLERK 0 0 1900
20 ANALYST 0 0 6000
20 MANAGER 0 0 2975
20 0 1 10875
<b>another break... another subtotal...</b>
30 CLERK 0 0 950
30 MANAGER 0 0 2850
30 SALESMAN 0 0 5600
30 0 1 9400
<b>and the last subtotal by deptno, now for the grand total:</b>
1 1 32025
15 rows selected.
<b>and we are done.... group by rollup is designed to do that for us. Now onto cube:</b>
ops$tkyte@ORA9IR2> select deptno, job, grouping(deptno), grouping(job), sum(sal)
2 from emp
3 group by cube(deptno,job)
4 /
DEPTNO JOB GROUPING(DEPTNO) GROUPING(JOB) SUM(SAL)
---------- --------- ---------------- ------------- ----------
0 0 3000
0 1 3000
1 0 3000
1 1 32025
<b>there are all of the NULL deptno/jobs</b>
CLERK 1 0 4150
ANALYST 1 0 6000
MANAGER 1 0 8275
SALESMAN 1 0 5600
PRESIDENT 1 0 5000
<b>all of the NULL deptno by job</b>
10 0 1 8750
10 CLERK 0 0 1300
10 MANAGER 0 0 2450
10 PRESIDENT 0 0 5000
<b>all of the deptno=10 by job, and so on...</b>
20 0 1 10875
20 CLERK 0 0 1900
20 ANALYST 0 0 6000
20 MANAGER 0 0 2975
30 0 1 9400
30 CLERK 0 0 950
30 MANAGER 0 0 2850
30 SALESMAN 0 0 5600
21 rows selected.
<b>it is a "cube", it cannot be presented in some report like format (well, it could but it would be hugely expensive to have report by report -- report the details, report summed over dimension 1, report summed over dimension 2, report summed over dimension 1 and 2 and so on....) So they do it data value by data value</b>
Hope that helps.
Nice
Ram, March 22, 2004 - 3:18 am UTC
Dear Sir,
Do you have any simple examples for using the functions
"Group_id" and "Grouping_id"?
Please do reply.
Bye!
show report name at run time
tarek zan, April 13, 2004 - 6:57 am UTC
how i show the the name of the report with extension (rdf)at the end of the report at run time
tree
A reader, May 17, 2004 - 9:05 am UTC
how i use tree in developer
explain plan of rollup
friend, August 17, 2004 - 12:43 am UTC
Hi Tom,
How are you ? I am fine and doing well.
Pleas ehelp me to understand following explain plan.
Thanks!
Regards,
SQL> @1
TIME_ID C PROD_ID SALES
--------- - ---------- ----------
01-DEC-99 C 45 79
01-DEC-99 I 20 266
01-DEC-99 S 45 316
01-DEC-99 T 45 790
02-DEC-99 C 20 14
02-DEC-99 C 45 4187
02-DEC-99 T 45 158
01-DEC-99 C 79
01-DEC-99 I 266
01-DEC-99 S 316
01-DEC-99 T 790
02-DEC-99 C 4201
02-DEC-99 T 158
C 20 14
C 45 4266
I 20 266
S 45 316
T 45 948
18 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=38)
1 3 RECURSIVE EXECUTION OF 'SYS_LE_3_0'
2 3 RECURSIVE EXECUTION OF 'SYS_LE_3_1'
3 0 TEMP TABLE TRANSFORMATION
4 3 VIEW (Cost=2 Card=2 Bytes=76)
5 4 VIEW (Cost=2 Card=2 Bytes=76)
6 5 UNION-ALL
7 6 TABLE ACCESS (FULL) OF 'SYS_TEMP_1_0_FD9D6644' (Co
st=1 Card=1 Bytes=38)
8 6 TABLE ACCESS (FULL) OF 'SYS_TEMP_1_0_FD9D6645' (Co
st=1 Card=1 Bytes=38)
Statistics
----------------------------------------------------------
8 recursive calls
46 db block gets
10002 consistent gets
4216 physical reads
1188 redo size
1563 bytes sent via SQL*Net to client
634 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
18 rows processed
SQL> l
1 SELECT time_id, channel_id, prod_id,
2 ROUND(SUM(amount_sold)) AS sales
3 FROM sales
4 WHERE (time_id = '01-DEC-1999' or time_id = '02-DEC-1999')
5 AND prod_id IN (10, 20, 45)
6 GROUP BY GROUPING SETS
7 ((time_id, channel_id, prod_id),
8 (time_id, channel_id),
9* (channel_id, prod_id))
SQL>
A unique Problem - need direction
Andy, August 31, 2004 - 4:12 pm UTC
Hi Tom,
Having probelm to figure out how to write a query, I feel I have to write a GROUP BY CUBE but need direction.
I have 8 transaction tables.
For ex. TR1 - TR8
Each table has a unique ID , say UID. Each TR1-TR8 tables have corresponnding Profile Table TP1-TP2. Now the relation between TP and TR (TP1 & TR1 for eg.) is ONE TO MANY, using UID. TP table's have a another UNIQUE Identifier along with UID, lets say PID. Now that PID can be anywhere be present in any ONE or MORE of other table's (for eg. TP1.PID = TP2.PID and also TP1.PID = TP5.PID).
I have to create a report like :
T1 T2 T3 T4 T5 T6 T7 T8 T9 UniqueIND
Y N N N N N N N N 4,000
Y Y N N N N N N N 1,000
Y Y Y N N N N N N 300
Y Y Y Y N N N N N 150
and so on...
What the above means :
Row 1 : Unique number of PID's in T1 is 4000
Row 2 : Unique number of PID's in T1 & T2 1000
Row 3 : Unique number of PID's in T1 & T2 & T3 300
Row 4 : Unique number of PID's in T1 & T2 & T3 & T4 150
...
...
Row 9 : Unique number of PID's in T2 is 500 ( not shown )
Row 10: Unique number of PID's in T2 & T1 1000 ( as Row 2)
So on...
Eg. Of table structure showing only the ID's
TP1 TR1
------ ------
PID TXNO
UID UID
TP2 TR2
----- ------
PID TXNO
UID UID
Can you please guide me ?
August 31, 2004 - 5:26 pm UTC
don't get it -- you flipped from TP and TR to just "T"
TP1..TP2
TR1..TR8
but then there is t1..t9?
Y/N/N means what? that the table was included? or that there were rows in the table?
seems like TP* are the only relevant tables -- does TR* even come into play?
Why would you have row 10 at all ? seems row two has already given you that exact fact?
Sorry for incomplete details....
Andy, August 31, 2004 - 9:56 pm UTC
Here is the thing....
Eg. Of table structure showing only the ID's and Cols in Report.
TP1 TR1
------ ------
PID TXNO
UID UID
T1 T1
TP2 TR2
----- ------
PID TXNO
UID UID
T2 T2
.......
.......
TP8 TR8
----- ------
PID TXNO
UID UID
T8 T8
There are 8 Profile table TP1...TP8
There are 8 Transaction table TP1...TP8
I have to create a report like :
T1 T2 T3 T4 T5 T6 T7 T8 UniqueIND
Y N N N N N N N 4,000
Y Y N N N N N N 1,000
Y Y Y N N N N N 300
Y Y Y Y N N N N 150
Now what Y/N/N means....
Row 1 : PID in T1 exclusive , doesn't occur in any other
tables, Unique number of PID's in T1 is 4000 so "Y"
on T1 but "N" on rest.
Row 2 : PID's in T1 intersect T2, Unique number of PID's in
T1 & T2 is 1000. So "Y" on T1 and "Y" on T2 but "N"
on rest.
Row 3 : PID's in T1 intersect T2 intersect T3, Unique number
of PID's in T1 & T2 & T3 1000. So "Y" on T1 and
"Y" on T2 and "Y" on T3 but "N" on rest.
Goes like this upto Row 8 when the cycle starts again.
" seems like TP* are the only relevant tables -- does TR* even come into play? "
As I told you earlier....
PID is unique across all the TP's(if overlap happens) but not UID's. SO we have to pick up PID's and try finding them in other TP's. SO we need TP's to find out PID's and then search others TP's for same PID's.
let me give you this in set theory...
The values show are only PID's not UID's
TP1 = {1,2,3,4,5,7,8,9}
TP2 = {2,3,9,10,11,12,13}
TP3 = {1,3,5,9,20,30,40,50}
TP4 = {1,2,3,4,50,60,70}
Now the report should look like...
T1 T2 T3 T4 UniqueIND
--- --- --- --- ------
Y N N N 3 {7,8,9}
Y Y N N 1 {2}
Y Y Y N 3 {2,3,9}
Y Y Y Y 1 {3}
N Y N N 4 {10,11,12,13}
N Y Y N 5 {2,10,11,12,13}
N Y Y Y 6 {2,9,10,11,12,13}
its like TP1.PID...TP1.PID Intersects TP2.PID.... TP1.PID Intersects TP2.PID Intersects TP3.PID etc etc....
Hope I could make you understand. :(
Appreciate your time and patience....
September 01, 2004 - 7:49 am UTC
sorry -- not getting it, you are not being really careful here (remember -- these tables to you are second nature, you own them, you live with them every day. to everyone else on the planet -- they are "not normal", "something new", "different", "unknown")
simple unclear things like:
There are 8 Profile table TP1...TP8
There are 8 Transaction table TP1...TP8
tp1..tp8 and tp1..tp8.... hmmm.
and you say:
</quote>
Row 1 : PID in T1 exclusive , doesn't occur in any other
tables, Unique number of PID's in T1 is 4000 so "Y"
on T1 but "N" on rest.
<quote>
what the HECK is T1 -- looks a ton like a *column* in this example, but you keep refering to it as a TABLE?!?
In fact, when you say T1 interect T2 -- well, thats "table semantics", but T1 is apparently a column -- soooooooo sorry -- when I'm taking questions we can try over there but the specification here is just "beyond my ability to understand the question" (eg: no need to followup here)
Rollup by date?
A reader, October 12, 2004 - 2:20 pm UTC
Sir
can you explain how you would provde the following date for cross tab report to could the number of records per month?
organization Jan Feb Mar April
ford 5 21 12 121
toyota 1 0 0 45
chevy 3 2 1 1
October 12, 2004 - 2:24 pm UTC
select org,
count( decode( trunc(dt,'mm'), to_date('01-jan-2004','dd-mon-yyyy') ) jan,
count( decode( trunc(dt,'mm'), to_date('01-feb-2004','dd-mon-yyyy') ) feb,
...
count( decode( trunc(dt,'mm'), to_date('01-dec-2004','dd-mon-yyyy') ) dec
from t
group by org;
Decode in View
Mohan, October 13, 2004 - 9:35 am UTC
Tom,
I have a view
entity_key key_value type_name .......
1 100 Member............
2 100 Dependent...........
3 100 Beneficiary..........
Is there any way i can display the data something like this
key_value Type_name
100 Member,Dependent,Beneficiary.
Thanks
October 13, 2004 - 9:56 am UTC
more left unsaid than said -- but yes, you can do that.
search this site for
stragg
for one approach, or
pivot
for other "just sql" approaches.
you can easily use rownum OR row_number() to pivot that
A reader, October 15, 2004 - 10:45 am UTC
Sir
thank you for the reply on the cross tab report it was much helpful. can you provide me how to use the same syntax but how I can keep a running count and not just for one month that is specific for one year?
organization Nov03 Dec03 Jan04 Feb04
ford 5 7 12 14
toyota 1 1 3 3
chevy 3 4 5 5
(ford had 5 in nov, 2 new in dec but 7 overall etc)
October 15, 2004 - 12:01 pm UTC
does not compute. not understanding the question.
running count of WHAT exactly?????
If I think I understand you (grasping at straws)
take:
select org,
count( decode( trunc(dt,'mm'), to_date('01-jan-2004','dd-mon-yyyy') )
jan,
count( decode( trunc(dt,'mm'), to_date('01-feb-2004','dd-mon-yyyy') )
feb,
...
count( decode( trunc(dt,'mm'), to_date('01-dec-2004','dd-mon-yyyy') ) dec
from t
group by org;
call it "Q"
select org, jan, jan+feb, jan+feb+mar, jan+feb+mar+apr, .....
from (Q)
/
A reader, October 15, 2004 - 12:58 pm UTC
Sir
oh so sorry. running count of users that use these cars so in this example ford had 5 users,then in dec they added two more to make a total of seven so I need this in the report to add together and also not to be year specific so 03 nubmers and add to 04 and so on
organization Nov03 Dec03 Jan04 Feb04
ford 5 7 12 14
toyota 1 1 3 3
chevy 3 4 5 5
October 15, 2004 - 3:39 pm UTC
like I said -- add columns, there you go.
the example above shows the technique to apply
A reader, October 15, 2004 - 1:11 pm UTC
Sir
how can you do this Q query in your example with clause?
October 15, 2004 - 3:40 pm UTC
? not following you.
A reader, October 15, 2004 - 3:52 pm UTC
Sir
sorry you say call it "Q"
select org, jan, jan+feb, jan+feb+mar, jan+feb+mar+apr, .....
from (Q)
how you do this?
October 15, 2004 - 6:01 pm UTC
c/Q/original query/
just put the query in there, the one I said "call this Q"
select ...
from ( select ..... )
A reader, October 27, 2004 - 4:57 pm UTC
Hi Tom,
Could you please tell what will happen when we use the following combination of the GROUP BY and ROLLUP
GROUP BY (DEPTNO,MGR,JOB) ROLLUP (DEPTNO,MGR)
GROUP BY (DEPTNO,MGR,JOB) ROLLUP (DEPTNO,(MGR,JOB))
GROUP BY ROLLUP (DEPTNO,(MGR,JOB))
Thanks
October 27, 2004 - 6:00 pm UTC
ops$tkyte@ORA9IR2> select deptno, mgr, job, sum(sal),
2 grouping(deptno),
3 grouping(mgr),
4 grouping(job)
5 from emp
6 group by (DEPTNO,MGR,JOB) ROLLUP (DEPTNO,MGR)
7 /
group by (DEPTNO,MGR,JOB) ROLLUP (DEPTNO,MGR)
*
ERROR at line 6:
ORA-00933: SQL command not properly ended
ops$tkyte@ORA9IR2> select deptno, mgr, job, sum(sal),
2 grouping(deptno),
3 grouping(mgr),
4 grouping(job)
5 from emp
6 GROUP BY (DEPTNO,MGR,JOB) ROLLUP (DEPTNO,(MGR,JOB))
7 /
GROUP BY (DEPTNO,MGR,JOB) ROLLUP (DEPTNO,(MGR,JOB))
*
ERROR at line 6:
ORA-00933: SQL command not properly ended
ops$tkyte@ORA9IR2> select deptno, mgr, job, sum(sal),
2 grouping(deptno) gdeptno,
3 grouping(mgr) gmgr,
4 grouping(job) gjob
5 from emp
6 GROUP BY ROLLUP (DEPTNO,(MGR,JOB))
7 /
DEPTNO MGR JOB SUM(SAL) GDEPTNO GMGR GJOB
---------- ---------- --------- ---------- ---------- ---------- ----------
10 PRESIDENT 5000 0 0 0
10 7782 CLERK 1300 0 0 0
10 7839 MANAGER 2450 0 0 0
10 8750 0 1 1
20 7566 ANALYST 6000 0 0 0
20 7788 CLERK 1100 0 0 0
20 7839 MANAGER 2975 0 0 0
20 7902 CLERK 800 0 0 0
20 10875 0 1 1
30 7698 CLERK 950 0 0 0
30 7698 SALESMAN 5600 0 0 0
30 7839 MANAGER 2850 0 0 0
30 9400 0 1 1
29025 1 1 1
14 rows selected.
The 1's and 0's in the valid syntax show which dimension was aggregated over.
ROLLUP IN PL/SQL - Oracle 8i
ande, January 16, 2005 - 1:59 pm UTC
Hi Tom,
i read in the web that you said that rollup doesn't work
in plsql Oracle 8i but i 've done it in plsql in oracle 8i:
INSERT INTO....
(SELECT...
ROLLUP)
A reader, May 16, 2005 - 1:49 pm UTC
Hi Tom,
I am trying to understand how ROLLUP and CUBE work. Can you explain me with example how they work? We are working on generating a large number of reports in our project and I want to know when I should use ROLLUP/CUBE or when to use analytic functions with "over (partition by". Please help.
Thanks.
May 16, 2005 - 3:59 pm UTC
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1512805503041 <code>
use rollup and cube to create aggregate records.
use analytics when you need all of the details, but need to work across rows as well.
eg: group by's can give you the sum(sal) by deptno by summing of the details
and removing them from the result set.
sum(deptno) over (....) can give you the sum of sal by deptno attached to
every detail row.
Example
A reader, May 16, 2005 - 5:20 pm UTC
Hi Tom,
I have a table as
create table testacc
(
loc varchar2(20),
iss number,
rep number
);
insert into testacc values ('loc1', 20, 1);
insert into testacc values ('loc1', 40, 2);
insert into testacc values ('loc1', 35, 2);
insert into testacc values ('loc1', 55, 1);
insert into testacc values ('loc2', 60, 7);
insert into testacc values ('loc2', 70, 8);
commit;
I did a query as follows:
select grouping (loc), substr(loc,1,5), grouping(iss), iss, grouping(rep), rep
from testacc
group by rollup(loc, iss, rep);
and the results of this query are:
GROUPING(LOC) SUBST GROUPING(ISS) ISS GROUPING(REP) REP
------------- ----- ------------- ---------- ------------- ----------
0 loc1 0 20 0 1
0 loc1 0 20 1
0 loc1 0 35 0 2
0 loc1 0 35 1
0 loc1 0 40 0 2
0 loc1 0 40 1
0 loc1 0 55 0 1
0 loc1 0 55 1
0 loc1 1 1
0 loc2 0 60 0 7
0 loc2 0 60 1
0 loc2 0 70 0 8
0 loc2 0 70 1
0 loc2 1 1
1 1 1
I need the results more like
loc iss rep
--- ---- ----
loc1 20 1
loc1 40 2
loc1 55 1
loc1 35 2
**subtotal 150 6
loc2 60 7
loc2 70 8
**subtotal 130 15
**grandtotal 280 21
Thanks.
May 16, 2005 - 5:43 pm UTC
ops$tkyte@ORA9IR2> select decode( grouping(rowid), 1, decode(grouping(loc),1,'Grandtotal', 'Subtotal'), loc ) loc, sum(iss), sum(rep)
2 from testacc
3 group by rollup( loc, rowid )
4 /
LOC SUM(ISS) SUM(REP)
-------------------- ---------- ----------
loc1 20 1
loc1 40 2
loc1 35 2
loc1 55 1
Subtotal 150 6
loc2 60 7
loc2 70 8
Subtotal 130 15
Grandtotal 280 21
9 rows selected.
A reader, May 17, 2005 - 10:55 am UTC
Hi Tom,
This works great. I have just one more question. In the above example if I want to include the rownumber with the result like as follows:
LOC SUM(ISS) SUM(REP) rownumber
-------------------- ---------- ----------
loc1 20 1 1
loc1 40 2 2
loc1 35 2 3
loc1 55 1 4
**Subtotal 150 6 5
loc2 60 7 6
loc2 70 8 7
**Subtotal 130 15 8
**Grandtotal 280 21 9
How can I do it?
Thanks.
May 17, 2005 - 2:04 pm UTC
select q.*, rownum
from ( that query ) Q
/
A reader, May 19, 2005 - 3:01 pm UTC
Hi Tom,
For the previous example I have to select an extra column now like
select a.*, rownum from (select decode( grouping(rowid), 1,
decode(grouping(loc),1,'Grandtotal', 'Subtotal'), loc ) loc, sum(iss), sum(rep), des
from testacc
group by rollup( loc, rowid ) ) a
/
(note that in the select clause ,des is the new addition)
but when I try to execute it, it is expecting me to include des in the group by expression but i don't want to do it because I don't want the totals to be grouped by column des. How can I overcome this?
Thanks.
May 19, 2005 - 4:25 pm UTC
well, what should be the behavior of des exactly then. example called for.
you aggregate over loc - which "des" value should "appear" - you could use max, min, some other aggregate
but you either aggregate it (sum, count, whatever) or you aggregate by it. otherwise, it has no meaning in the query.
A reader, May 19, 2005 - 3:02 pm UTC
Another followup to previous question. To make life easier for you,
alter table testacc add des varchar(20);
Thanks.
Followup to previous question with an example
A reader, May 19, 2005 - 4:57 pm UTC
Hi Tom,
For an example I need something like :
create table testacc
(
loc varchar2(20),
iss number,
rep number,
des varchar2(8)
);
insert into testacc values ('loc1', 20, 1,'DD');
insert into testacc values ('loc1', 40, 2,'DD');
insert into testacc values ('loc1', 35, 2,'DD');
insert into testacc values ('loc1', 55, 1,'DL');
insert into testacc values ('loc2', 60, 7,'DD');
insert into testacc values ('loc2', 70, 8,'DL');
commit;
Right now I have
select a.*, rownum from (select decode( grouping(rowid), 1,
decode(grouping(loc),1,'Grandtotal', 'Subtotal'), loc ) loc, sum(iss), sum(rep)
from testacc
group by rollup( loc, rowid ) ) a
/
which results in
LOC SUM(ISS) SUM(REP) ROWNUM
-------------------- ---------- ---------- ----------
loc1 20 1 1
loc1 40 2 2
loc1 35 2 3
loc1 55 1 4
Subtotal 150 6 5
loc2 60 7 6
loc2 70 8 7
Subtotal 130 15 8
Grandtotal 280 21 9
But I want the results as
LOC DES SUM(ISS) SUM(REP) ROWNUM
------------------- --- ---------- ---------- ----------
loc1 DD 20 1 1
loc1 DD 40 2 2
loc1 DD 35 2 3
loc1 DL 55 1 4
Subtotal 150 6 5
loc2 DD 60 7 6
loc2 DL 70 8 7
Subtotal 130 15 8
Grandtotal 280 21 9
Thanks.
May 20, 2005 - 7:03 am UTC
tkyte@ORA9IR2W> select a.*, rownum
2 from (select decode( grouping(rowid), 1, decode(grouping(loc),1,'Grandtotal', 'Subtotal'), loc ) loc,
3 max(des),
4 sum(iss), sum(rep)
5 from testacc
6 group by grouping sets( ( loc, des, rowid ), (loc) ) ) a
7 /
LOC MAX(DES) SUM(ISS) SUM(REP) ROWNUM
-------------------- -------- ---------- ---------- ----------
loc1 DD 20 1 1
loc1 DD 40 2 2
loc1 DD 35 2 3
loc1 DL 55 1 4
Subtotal DL 150 6 5
loc2 DD 60 7 6
loc2 DL 70 8 7
Subtotal DL 130 15 8
8 rows selected.
A reader, May 20, 2005 - 11:40 am UTC
Hi Tom,
This works great. The only problem is the Grandtotal does not come up. It is giving only the subtotal.
Thanks.
May 20, 2005 - 6:35 pm UTC
ops$tkyte@ORA10GR1> select a.*, rownum
2 from (select decode( grouping(rowid), 1, decode(grouping(loc),1,'Grandtotal', 'Subtotal'), loc ) loc,
3 decode( grouping(loc),1,'',max(des)),
4 sum(iss), sum(rep)
5 from testacc
6 group by grouping sets( ( loc, des, rowid ), (loc), () ) ) a
7 /
LOC DECODE(G SUM(ISS) SUM(REP) ROWNUM
-------------------- -------- ---------- ---------- ----------
loc1 DD 20 1 1
loc1 DD 40 2 2
loc1 DD 35 2 3
loc1 DL 55 1 4
Subtotal DL 150 6 5
loc2 DD 60 7 6
loc2 DL 70 8 7
Subtotal DL 130 15 8
Grandtotal 280 21 9
9 rows selected.
group by rollup help
ht, May 26, 2005 - 8:27 pm UTC
Hi Tom,
I'm trying to understand the "group by rollup/grouping sets" functionality by producing a report that displays the total number of people hired and salesmen hired for each month.
SQL> select
2 to_char(trunc(hiredate,'Mon'),'Mon yyyy') month1,
3 (count(*)) total_hired,
4 salesmen.salesmen_hired
5 from
6 scott.emp,
7 (select trunc(hiredate,'Mon') month2,count(*) salesmen_hired from scott.emp
8 where job='SALESMAN' group by trunc(hiredate,'Mon'))salesmen
9 where trunc(hiredate,'Mon')=salesmen.month2(+)
group by rollup
10 11 (trunc(hiredate,'Mon'),salesmen.salesmen_hired),();
MONTH1 TOTAL_HIRED SALESMEN_HIRED
-------- ----------- --------------
Dec 1980 1
Dec 1980 1
Feb 1981 2 2
Feb 1981 2
Apr 1981 1
Apr 1981 1
May 1981 1
May 1981 1
Jun 1981 1
Jun 1981 1
Sep 1981 2 2
Sep 1981 2
Nov 1981 1
Nov 1981 1
Dec 1981 2
Dec 1981 2
Jan 1982 1
Jan 1982 1
Apr 1987 1
Apr 1987 1
May 1987 1
May 1987 1
14
23 rows selected.
How would I adjust the output to look like the following?
MONTH1 TOTAL_HIRED SALESMEN_HIRED
-------- ----------- --------------
Dec 1980 1
Feb 1981 2 2
Apr 1981 1
May 1981 1
Jun 1981 1
Sep 1981 2 2
Nov 1981 1
Dec 1981 2
Jan 1982 1
Apr 1987 1
May 1987 1
14 4
As always, thank you for your assistance.
ht
May 27, 2005 - 7:35 am UTC
you overdid the query, use CASE or DECODE to count, not all of those subqueries.
scott@ORA9IR2> select to_char(hiredate,'Mon YYYY'), count(*) tot_hired, count(case when job='SALESMAN' then 1 end) sales_hired
2 from emp
3 group by rollup(to_char(hiredate,'Mon YYYY'))
4 order by to_date(to_char(hiredate,'Mon YYYY'),'Mon YYYY')
5 /
TO_CHAR( TOT_HIRED SALES_HIRED
-------- ---------- -----------
Dec 1980 1 0
Feb 1981 2 2
Apr 1981 1 0
May 1981 1 0
Jun 1981 1 0
Sep 1981 2 2
Nov 1981 1 0
Dec 1981 2 0
Jan 1982 1 0
Dec 1982 1 0
Jan 1983 1 0
14 4
12 rows selected.
Thank you.
ht, May 27, 2005 - 9:29 am UTC
How to calculate ...
ht, May 27, 2005 - 5:18 pm UTC
Tom,
I've been expanding on your solution and am now wondering how to display the number employees that took a vacation day in a certain month. This would be displayed alongside the month and total number of vacation days taken in the company.
SQL> conn scott/tiger
Connected.
SQL>
SQL> drop table vacation_taken;
Table dropped.
SQL>
SQL> create table vacation_taken
2 (
3 empno number,
4 vacation_day date
5 );
Table created.
SQL>
SQL> insert into vacation_taken values (7369,to_date('12/18/1980','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7369,to_date('12/20/1980','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7369,to_date('12/25/1980','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7369,to_date('03/15/1981','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7369,to_date('03/25/1981','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7369,to_date('03/17/1981','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7369,to_date('04/15/1981','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7369,to_date('12/25/1981','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7369,to_date('02/25/1983','mm/dd/yyyy'));
1 row created.
SQL>
SQL> insert into vacation_taken values (7839,to_date('11/18/1981','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7839,to_date('12/25/1981','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7839,to_date('02/15/1983','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7839,to_date('02/25/1983','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7839,to_date('03/15/1983','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7839,to_date('03/25/1983','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7839,to_date('03/17/1983','mm/dd/yyyy'));
1 row created.
SQL> insert into vacation_taken values (7839,to_date('04/15/1983','mm/dd/yyyy'));
1 row created.
SQL>
SQL> select
2 to_char(trunc(vacation_day,'Mon'),'Mon yyyy') month,
3 count(*) tot_emp_vdays,
4 (select count(*) from emp)number_emps_that_took_vday
5 from vacation_taken
6 group by rollup
7 (
8 trunc(vacation_day,'Mon')
9 );
MONTH TOT_EMP_VDAYS NUMBER_EMPS_THAT_TOOK_VDAY
-------- ------------- --------------------------
Dec 1980 3 14
Mar 1981 3 14
Apr 1981 1 14
Nov 1981 1 14
Dec 1981 2 14
Feb 1983 3 14
Mar 1983 3 14
Apr 1983 1 14
17 14
9 rows selected.
>>>>>>>>>>> How do I modify the 3rd column to display the number of emps that took a vacation day in that month?
SQL>
SQL> select
2 to_char((trunc(hiredate,'Mon')),'Mon yyyy') month,
3 count(*) employees_hired
4 from emp
5 group by rollup
6 (trunc(hiredate,'Mon'));
MONTH EMPLOYEES_HIRED
-------- ---------------
Dec 1980 1
Feb 1981 2
Apr 1981 1
May 1981 1
Jun 1981 1
Sep 1981 2
Nov 1981 1
Dec 1981 2
Jan 1982 1
Apr 1987 1
May 1987 1
14
12 rows selected.
Thank you.
ht
May 27, 2005 - 5:37 pm UTC
I don't understand why you are going after the EMP table here at all.
to count the days of vacation and the count of distinct employees taking that vacation by month:
1 select trunc(vacation_day,'mm'), count(*), count(distinct empno)
2 from vacation_taken
3* group by trunc(vacation_day,'mm')
ops$tkyte@ORA9IR2> /
TRUNC(VAC COUNT(*) COUNT(DISTINCTEMPNO)
--------- ---------- --------------------
01-DEC-80 3 1
01-MAR-81 3 1
01-APR-81 1 1
01-NOV-81 1 1
01-DEC-81 2 2
01-FEB-83 3 2
01-MAR-83 3 1
01-APR-83 1 1
8 rows selected.
Clarification on the last post ...
ht, May 27, 2005 - 5:21 pm UTC
I actually want to display the number of employees that were eligible to take a vacation day during the month.
Month VdayTaken Emps
Jan 1980 3 8
Feb 1981 7 14
May 27, 2005 - 5:40 pm UTC
ops$tkyte@ORA9IR2> select x.*, count(emp.empno)
2 from (
3 select trunc(vacation_day,'mm') mm, count(*) cnt1, count(distinct empno) cnt2
4 from vacation_taken
5 group by trunc(vacation_day,'mm')
6 ) X, emp
7 where emp.hiredate(+) <= x.mm
8 group by x.mm, x.cnt1, x.cnt2
9 /
MM CNT1 CNT2 COUNT(EMP.EMPNO)
--------- ---------- ---------- ----------------
01-DEC-80 3 1 0
01-MAR-81 3 1 3
01-APR-81 1 1 3
01-NOV-81 1 1 8
01-DEC-81 2 2 9
01-FEB-83 3 2 14
01-MAR-83 3 1 14
01-APR-83 1 1 14
8 rows selected.
Perfect!
ht, May 27, 2005 - 6:08 pm UTC
A reader, October 04, 2005 - 7:28 am UTC
A reader, October 04, 2005 - 7:28 am UTC
READER
A reader, October 27, 2005 - 5:19 pm UTC
Hi Tom
the question is
#using grouping sets write a query to display the folowing
-department_id,manager_id,job_id
-department_id,job_id
-manager_id,job_id
the query should calculate the sum of the salaries for each of these groups.
the solution is:
select department_id,manager_id,job_id,sum(salary)
from employees
group by
grouping sets ((department_id,manager_id,job_id),(department_id,job_id),(manager_id,job_id));
my question is can't solution be like this:
select department_id,manager_id,job_id,sum(salary)
from employees
group by grouping sets(department_id,manager_id),grouping sets(job_id);
thanks
sachin
October 28, 2005 - 1:49 am UTC
well, if you run them, you would see 'no, is can't be that'
scott@ORA10GR2> select deptno,mgr,job,sum(sal)
2 from emp
3 group by
4 grouping sets
5 ((deptno,mgr,job),(deptno,job),(mgr,job));
DEPTNO MGR JOB SUM(SAL)
---------- ---------- --------- ----------
10 PRESIDENT 5000
20 7566 ANALYST 6000
30 7698 CLERK 950
30 7698 SALESMAN 5600
10 7782 CLERK 1300
20 7788 CLERK 1100
10 7839 MANAGER 2450
20 7839 MANAGER 2975
30 7839 MANAGER 2850
20 7902 CLERK 800
PRESIDENT 5000
7566 ANALYST 6000
7698 CLERK 950
7698 SALESMAN 5600
7782 CLERK 1300
7788 CLERK 1100
7839 MANAGER 8275
7902 CLERK 800
30 CLERK 950
30 SALESMAN 5600
20 CLERK 1900
20 MANAGER 2975
10 PRESIDENT 5000
10 CLERK 1300
30 MANAGER 2850
20 ANALYST 6000
10 MANAGER 2450
27 rows selected.
scott@ORA10GR2>
scott@ORA10GR2>
scott@ORA10GR2> select deptno,mgr,job,sum(sal)
2 from emp
3 group by grouping sets(deptno,mgr),grouping sets(job);
DEPTNO MGR JOB SUM(SAL)
---------- ---------- --------- ----------
7698 SALESMAN 5600
7902 CLERK 800
7788 CLERK 1100
PRESIDENT 5000
7566 ANALYST 6000
7839 MANAGER 8275
7698 CLERK 950
7782 CLERK 1300
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000
17 rows selected.
different answers.
reader
A reader, October 28, 2005 - 10:42 am UTC
concerning your answer immediately------
what i mean to say is that when i write the solution as
------------------
select department_id,manager_id,job_id,sum(salary)
from employees
group by grouping sets(department_id,manager_id),grouping sets(job_id);
-------------------
according to rules will it not make the following sets for the above two grouping sets:
a)department_id,manager_id,job_id
b)department_id,job_id
c)manager_id,job_id
and in QUESTION also he WANTS groupings as above(a,b,c)..so why not my answer is correct(i know the output is diferent)...please explain.
October 28, 2005 - 1:19 pm UTC
this is a big page, so - not really sure which "he" you refer to?
READER
A reader, October 28, 2005 - 1:25 pm UTC
Hi Tom
"He" i immediately above....
thanks
sachin
October 28, 2005 - 2:54 pm UTC
so, where are your:
department_id,manager_id,job_id
records?
reader
A reader, October 28, 2005 - 1:27 pm UTC
"He" is immediately above
thanks
sachin
October 29, 2005 - 10:32 am UTC
Umm,
immediately above would be you and me....
how about being a tad more "precise"?
Maybe I'm being *dense* here but after a quick scan, I don't even see the query you are talking about:
....
the solution is:
select department_id,manager_id,job_id,sum(salary)
from employees
group by
grouping sets
((department_id,manager_id,job_id),(department_id,job_id),(manager_id,job_id));
......
On THIS PAGE until you mentioned it???? ctl-f for department_id from the top of this page and you get to ..... well, you?
And further, if the question is:
<quote, src=you>
#using grouping sets write a query to display the folowing
-department_id,manager_id,job_id
-department_id,job_id
-manager_id,job_id
</quote>
Please tell me where the "department_id,manager_id,job_id" records are in your query??????
not usefull
A reader, October 28, 2005 - 3:24 pm UTC
so here i am writing again uptill now what we have talked:
Hi Tom
the question is
#using grouping sets write a query to display the folowing
-department_id,manager_id,job_id
-department_id,job_id
-manager_id,job_id
the query should calculate the sum of the salaries for each of these groups.
the solution is:
select department_id,manager_id,job_id,sum(salary)
from employees
group by
grouping sets
((department_id,manager_id,job_id),(department_id,job_id),(manager_id,job_id));
my question is can't solution be like this:
select department_id,manager_id,job_id,sum(salary)
from employees
group by grouping sets(department_id,manager_id),grouping sets(job_id);
thanks
sachin
Followup:
well, if you run them, you would see 'no, is can't be that'
scott@ORA10GR2> select deptno,mgr,job,sum(sal)
2 from emp
3 group by
4 grouping sets
5 ((deptno,mgr,job),(deptno,job),(mgr,job));
DEPTNO MGR JOB SUM(SAL)
---------- ---------- --------- ----------
10 PRESIDENT 5000
20 7566 ANALYST 6000
30 7698 CLERK 950
30 7698 SALESMAN 5600
10 7782 CLERK 1300
20 7788 CLERK 1100
10 7839 MANAGER 2450
20 7839 MANAGER 2975
30 7839 MANAGER 2850
20 7902 CLERK 800
PRESIDENT 5000
7566 ANALYST 6000
7698 CLERK 950
7698 SALESMAN 5600
7782 CLERK 1300
7788 CLERK 1100
7839 MANAGER 8275
7902 CLERK 800
30 CLERK 950
30 SALESMAN 5600
20 CLERK 1900
20 MANAGER 2975
10 PRESIDENT 5000
10 CLERK 1300
30 MANAGER 2850
20 ANALYST 6000
10 MANAGER 2450
27 rows selected.
scott@ORA10GR2>
scott@ORA10GR2>
scott@ORA10GR2> select deptno,mgr,job,sum(sal)
2 from emp
3 group by grouping sets(deptno,mgr),grouping sets(job);
DEPTNO MGR JOB SUM(SAL)
---------- ---------- --------- ----------
7698 SALESMAN 5600
7902 CLERK 800
7788 CLERK 1100
PRESIDENT 5000
7566 ANALYST 6000
7839 MANAGER 8275
7698 CLERK 950
7782 CLERK 1300
20 CLERK 1900
30 SALESMAN 5600
20 MANAGER 2975
30 CLERK 950
10 PRESIDENT 5000
30 MANAGER 2850
10 CLERK 1300
10 MANAGER 2450
20 ANALYST 6000
17 rows selected.
different answers.
concerning your answer immediately------
what i mean to say is that when i write the solution as
------------------
select department_id,manager_id,job_id,sum(salary)
from employees
group by grouping sets(department_id,manager_id),grouping sets(job_id);
-------------------
according to rules will it not make the following groupings for the above two
grouping sets:
a)department_id,manager_id,job_id
b)department_id,job_id
c)manager_id,job_id
and in QUESTION also he WANTS groupings as above(a,b,c)..so why not my answer is correct(i know the output is diferent) when we see the logical groupings ...please explain.
October 29, 2005 - 10:42 am UTC
see above, I've no clue who "he" is, you are the first to use the word department_id on this page AND most importantly perhaps, your query does not return:
a)department_id,manager_id,job_id
so, now what?
need sum(sal) of employees working under employees.
Rajesh, December 15, 2005 - 12:07 pm UTC
Hi Tom,
This article was very informative.
Is it possible to write a query using Rollup which should give sum(sal) of employees working under employees. e.g. table like EMP.If yes then how.If not then is there any other way.
e.g. EMP table
empno mgrno ename sal
-------------------------
100 king 5000
101 100 adams 4000
102 100 smith 3000
103 101 allen 2500
104 101 martin 2200
105 101 miller 2000
The required o/p would be like this
empno ename sum(sal)
----------------------
100 king 7000
101 adams 6700
Grand Total 18700
Thanks...Rajesh
December 15, 2005 - 12:30 pm UTC
scott@ORA10GR2> select empno, ename, (select sum(sal) from emp e2 start with e2.empno = e1.empno connect by prior empno = mgr) sum_sal
2 from emp e1;
EMPNO ENAME SUM_SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 10875
7654 MARTIN 1250
7698 BLAKE 9400
7782 CLARK 3750
7788 SCOTT 4100
7839 KING 29025
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3800
7934 MILLER 1300
14 rows selected.
sum(sal) of all employees below level of that employee....
Rajesh, December 19, 2005 - 11:14 am UTC
Thanks Tom for your response.Actually I put the question wrong.Wery sorry for that.My requirement was getting sum(sal) of all employees below level of that employee....
e.g. EMP table
empno mgrno ename sal
-------------------------
100 king 5000
101 100 adams 4000
102 100 smith 3000
103 101 allen 2500
104 101 martin 2200
105 103 miller 2000
the required o/p is
empno ename sum(sal)
----------------------
100 king 13700
101 adams 8700
Grand Total 18700
Thanks...Rajesh
December 19, 2005 - 12:19 pm UTC
I don't get kings 13,700 value. I'm using the normal scott.emp table and I don't have your data.
Either provide your data in a format I can use (create table, insert into) or use the normal scott.emp data.
And explain how the numbers are arrived at. If you mean "everyone below 'KING' " for example, then just modify:
select empno, ename, (select sum(sal) from emp e2 start with
e2.empno = e1.empno connect by prior empno = mgr) sum_sal
from emp e1;
to start with e2.mgr = e1.empno
Decode / Union ALL
Preet, February 17, 2006 - 6:55 am UTC
Hi Tom,
SQL> select * from mytable;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
1. When I run the below query, I get the output in multiple lines. Is there some way I can get the output in a single line.
2. Also, please let me know if there is a better way to get the same result ( Some of my team members advised me to use UNION/ UNION ALL but I was not sure how to use them in this case).
The Query :-
select
decode(MYTABLE.DEPTNO,'10',decode(MYTABLE.JOB,'CLERK',count(MYTABLE.EMPNO))) AS "Category 1" ,
decode(MYTABLE.JOB,'SALESMAN',count(MYTABLE.EMPNO)) AS "Category 2" ,
decode(MYTABLE.DEPTNO,'20',decode(MYTABLE.JOB,'ANALYST',count(MYTABLE.EMPNO))) AS "Category 3" ,
decode(MYTABLE.DEPTNO,'30',decode(MYTABLE.JOB,'PRESIDENT',count(MYTABLE.EMPNO))) AS "Category 4 " ,
decode(MYTABLE.DEPTNO,'40',decode(MYTABLE.JOB,'CLERK',count(MYTABLE.EMPNO))) AS "Category 5" ,
decode(MYTABLE.DEPTNO,'40',decode(MYTABLE.JOB,'CLERK',count(MYTABLE.EMPNO))) AS "Category 6" ,
decode(MYTABLE.DEPTNO,'20',decode(MYTABLE.JOB,'MANAGER',count(MYTABLE.EMPNO))) AS "Category 7" ,
decode(MYTABLE.DEPTNO,'10',decode(MYTABLE.JOB,'SALESMAN',count(MYTABLE.EMPNO)))AS "Category 8"
from MYTABLE
where
trunc(MYTABLE.hiredate) <= trunc(sysdate)
group by MYTABLE.DEPTNO,MYTABLE.JOB;
Thanks in Advance
February 17, 2006 - 2:43 pm UTC
I'm not sure what your "goal" here is.
I think you want:
ops$tkyte@ORA10GR2> select count( case when deptno = 10 and job = 'CLERK' then 1 end ) cat1,
2 count( case when deptno = 20 and job = 'ANALYST' then 1 end ) cat2,
3 count( case when deptno = 30 and job = 'PRESIDENT' then 1 end ) cat3,
4 count( case when deptno = 40 and job = 'CLERK' then 1 end ) cat4,
5 count( case when deptno = 20 and job = 'MANAGER' then 1 end ) cat5,
6 count( case when deptno = 10 and job = 'SALESMAN' then 1 end ) cat6
7 from mytable
8 where hiredate < trunc(sysdate+1)
9 /
CAT1 CAT2 CAT3 CAT4 CAT5 CAT6
---------- ---------- ---------- ---------- ---------- ----------
1 2 0 0 1 0
but I'm not really sure
hiredate < trunc(sysdate+1) ???
Preet, February 17, 2006 - 11:59 pm UTC
Hi Tom,
Thanks ! This is what i was really looking for.
But i see that you have made some changed something in the WHERE clause of my query.
In place of
hiredate <= trunc(sysdate)
you have added
hiredate < trunc(sysdate+1).
Just wanted to know if there is some special reason to do so.
Thanks Again
February 18, 2006 - 8:29 am UTC
because you had:
trunc(MYTABLE.hiredate) <= trunc(sysdate)
when says "I want all hiredates that happened anytime TODAY or BEFORE"
and
hiredate < trunc(sysdate+1)
is the logical equivalent of that and says it more clearly (if you ask me) and efficiently. Avoids the function on the database column, allows the access path that uses an index on hiredate to be considered if appropriate.
I always remove functions from database columns when I can.
Why do we need an view
Babloo, April 17, 2006 - 12:14 pm UTC
Hi Tom ,
Sorry for a question on such an old question . refering to the first question on this page by Reddy , Why do we need a view there ?
April 17, 2006 - 8:05 pm UTC
I used a view to get a result set (table) that looked like their example, that is all.
I could have "create table as select", but just used a view to accomplish the same goal.
my view = their test_dept table
Dynamically change the groupings?
Krous, June 07, 2006 - 3:23 pm UTC
Is it possible to dynamically change the groupings based on user input ( like a parameter) without creating different queries..?
example: sub-totals by Emp or by Dept ( given by user as a parameter).
I believe these would basically be two queries which cannot be combined efficiently( different explain plan) and the way to go ahead would be to change the query at run time or have two queries with a union all.
select * from( ( q1 where p_select ='EMP')
union all
( q2 where p_select='DEPT'))
Thoughts/suggestions?
June 07, 2006 - 3:46 pm UTC
you would use dynamic sql and run the correct query - they are very very different queries and would each need their own unique plan.
Do we have any calender type table?
rahul, October 10, 2006 - 9:55 am UTC
Hi Tom,
I had a different set of problem. I have a table say of the following format
create table timesheet
(emp_id number,
project_id number,
ts_date date,
activity_id number,
time_in_hrs number);
Each person enter data for the activity he has done for that particular day (if any).
insert into timesheet values(111,1002,to_date('01/09/06','dd/mm/yy'),501,4);
insert into timesheet values(111,1002,to_date('01/09/06','dd/mm/yy'),502,4);
insert into timesheet values(112,1002,to_date('01/09/06','dd/mm/yy'),501,2);
insert into timesheet values(112,1002,to_date('01/09/06','dd/mm/yy'),512,2);
insert into timesheet values(111,1002,to_date('09/09/06','dd/mm/yy'),501,3);
insert into timesheet values(112,1002,to_date('02/09/06','dd/mm/yy'),501,4);
now say i have to generate a report for the given period and given project.
The report for the period 01/09 to 10/09 should look like this
emp_id date time_in_hrs
111 01/09 8
112 01/09 6
111 02/09 0
112 02/09 3
111 03/09 0
....
..
..
..
..
111 09/09 4
mean for all the days i don't have a entry for the particular employ it should have a blank entry.
One more thing for saturday and sundays hrs worked should be blank instead of 0.
Can this be achived through some query? or do we have to make use of some table.
Please help.
Rahul.
October 10, 2006 - 12:18 pm UTC
ops$tkyte%ORA10GR2> with data
2 as
3 (select to_date( '01-sep-2006', 'dd-mon-yyyy')+level-1 dt
4 from dual
5 connect by level <=
6 to_date( '10-sep-2006', 'dd-mon-yyyy')
7 -to_date( '01-sep-2006', 'dd-mon-yyyy')+1
8 )
9 select *
10 from timesheet partition by (emp_Id) right outer join data
11 on (data.dt = timesheet.ts_date)
12 order by data.dt, timesheet.emp_id
13 ;
EMP_ID PROJECT_ID TS_DATE ACTIVITY_ID TIME_IN_HRS DT
---------- ---------- --------- ----------- ----------- ---------
111 1002 01-SEP-06 502 4 01-SEP-06
111 1002 01-SEP-06 501 4 01-SEP-06
112 1002 01-SEP-06 512 2 01-SEP-06
112 1002 01-SEP-06 501 2 01-SEP-06
111 02-SEP-06
112 1002 02-SEP-06 501 4 02-SEP-06
111 03-SEP-06
112 03-SEP-06
111 04-SEP-06
112 04-SEP-06
111 05-SEP-06
112 05-SEP-06
111 06-SEP-06
112 06-SEP-06
111 07-SEP-06
112 07-SEP-06
111 08-SEP-06
112 08-SEP-06
111 1002 09-SEP-06 501 3 09-SEP-06
112 09-SEP-06
111 10-SEP-06
112 10-SEP-06
22 rows selected.
you will use bind variables not literals of course!
too good
rahul, October 17, 2006 - 6:51 am UTC
Thanks Tom,
The solution is good and seems to work only problem that i cannot filter data based on a given project id. IF we supply the project id it returns only the rows which have record in them.
I modified the query a bit
==========Modified query====================
with data as (select to_date( '10/03/06', 'mm/dd/yy')+level-1 dt
from dual connect by level <= to_date( '10/14/06', 'mm/dd/yy') -to_date( '10/03/06', 'mm/dd/yy')+1)
select t.emp_id, u.emp_name, nvl(t.project_id,123) project_id, dt,nvl(sum(time_in_hrs),0) time_in_hrs
from sym_timesheet t partition by (emp_Id) right outer join data on (data.dt = t.ts_date), sym_usermaster u
where t.emp_id=u.emp_id group by t.emp_id,project_id,u.emp_name,rollup (dt) order by t.emp_id,data.dt
=======================================
now if i add project_id = <literal> it returns data only for dates having record. Right outer join gets void.
Also to add to the complexity the user records needs to be picked from a seperate table
create table user_projects
( user_id number,
project_id number);
both being foreign key.
Their is one more problem with the modified query. I wanted the total hours for the individual employ so added rollup/cube but that introduces a blank record and then gives the total.
how do i avoid the additional record?
Thanks,
Rahul.
October 17, 2006 - 7:20 am UTC
9 select *
10 from (select * from timesheet where XXXXXXX )
partition by (emp_Id) right outer join data
11 on (data.dt = timesheet.ts_date)
apply predicate, then join.
ROLLUP ISSUE
Madhava Reddy, October 26, 2006 - 11:33 pm UTC
Hi Tom,
I am trying to execute the below ROLLUP query in scott schema(using 10g R2). Strangely I am getting "ORA-03113 end-of-file on commnucation channel" error.
select dname||decode(grouping(rownum),1,' Sub-total')
, empno
, ename
,sum(sal)
,grouping(empno) g1,grouping(ename) g2,grouping(dname) g3
,grouping(rownum) g4
from emp e,dept d
where e.deptno=d.deptno
group by rollup(dname,empno,ename,rownum)
having ( (grouping(empno)=0 and grouping(ename)=0 and grouping(dname)=0 and grouping(rownum)=0)
or (grouping(empno)=0 and grouping(ename)=0 and grouping(dname)=1 and grouping(rownum)=0))
can you please tell me if I made any mistake here? I am trying to simulate a report where in I have to display more than 4 columns and I need to show the Sub-Total.
October 27, 2006 - 7:41 am UTC
please utilize support for ora-3113, ora-600, ora-7445
Help in query
Rahul, November 02, 2006 - 7:06 am UTC
Hi Tom,
thanks for the query u had suggested.i have modified the query a little bit.
----------modified query is--------------
with data as
(select to_date( '10/03/06', 'mm/dd/yy')+level-1 dt
from dual connect by level <=
to_date( '10/18/06', 'mm/dd/yy')
-to_date( '10/03/06', 'mm/dd/yy')+1
)
select *
from (select * from (
select nvl(a.project_id,123)project_id,ts_date,b.emp_id ,sum(time_in_hrs) time_in_hrs
from sym_timesheet a right outer join sym_userprojects b on (b.EMP_ID = a.emp_id)
where b.project_id =123 group by ts_date, a.project_id,b.emp_id ) c where c.project_id =123 ) timesheet
partition by (emp_Id) right outer join data
on (data.dt = timesheet.ts_date)
order by timesheet.emp_id, data.dt
-----------------------------------------------------------
i modified it because i wanted the output for a particular project only. n all users of that project shud be selected even if they haevnt enterd the timesheet. so this query is giving me the output. now i want the sum of total hours for each emplyoee at the end of each emp's data.by using rollup i am getting the sum, but its giving a blank record afetr the sum. if the user has the sum as 0, its not giving the blank record. so i dont want that blank record for any emp. please help me with the query and modify the query for me using rollup.
November 02, 2006 - 7:17 am UTC
who is this "U" character and why are you asking me about their queries?
no tables
no inserts
no lookie
Report Query
Rahul, November 02, 2006 - 7:40 am UTC
Hi Tom,
Sorry for not giving you any references about the tables and lookups. Please refer 2-3 posts before this one. i have already provided the tables and some data.
*********************************************************
with data
as
(select to_date( '10/03/06', 'mm/dd/yy')+level-1 dt
from dual
connect by level <=
to_date( '10/18/06', 'mm/dd/yy')
-to_date( '10/03/06', 'mm/dd/yy')+1
)
select nvl(project_id,123) project_id,emp_id, nvl(sum(time_in_hrs),0) time_in_hrs, dt
from (select * from (
select nvl(a.project_id,123) project_id,ts_date,b.emp_id ,sum(time_in_hrs) time_in_hrs
from sym_timesheet a right outer join sym_userprojects b on (b.EMP_ID = a.emp_id)
where b.project_id =123 group by ts_date, a.project_id,b.emp_id ) c where c.project_id =123 ) timesheet
partition by (emp_Id) right outer join data
on (data.dt = timesheet.ts_date) group by emp_id, project_id, rollup(dt)
order by timesheet.emp_id, data.dt
**********************************************************
The output of this query is,
project_id emp_id time_inhrs dt
123 100222 12 10/3/2006
123 100222 0 10/4/2006
123 100222 7 10/5/2006
123 100222 0 10/6/2006
123 100222 0 10/7/2006
123 100222 0 10/8/2006
123 100222 0 10/9/2006
123 100222 0 10/10/2006
123 100222 0 10/11/2006
123 100222 0 10/12/2006
123 100222 0 10/13/2006
123 100222 0 10/14/2006
123 100222 0 10/15/2006
123 100222 0 10/16/2006
123 100222 0 10/17/2006
123 100222 0 10/18/2006
123 100222 0
123 100222 19
123 122222 0 10/3/2006
123 122222 0 10/4/2006
123 122222 0 10/5/2006
123 122222 0 10/6/2006
123 122222 0 10/7/2006
123 122222 0 10/8/2006
123 122222 0 10/9/2006
123 122222 0 10/10/2006
123 122222 0 10/11/2006
123 122222 0 10/12/2006
123 122222 0 10/13/2006
123 122222 0 10/14/2006
123 122222 0 10/15/2006
123 122222 0 10/16/2006
123 122222 0 10/17/2006
123 122222 0 10/18/2006
123 122222 0
123 143900 4 10/3/2006
123 143900 0 10/4/2006
123 143900 7 10/5/2006
123 143900 0 10/6/2006
123 143900 4 10/7/2006
123 143900 15 10/8/2006
123 143900 2 10/9/2006
123 143900 0 10/10/2006
123 143900 0 10/11/2006
123 143900 0 10/12/2006
123 143900 0 10/13/2006
123 143900 0 10/14/2006
123 143900 0 10/15/2006
123 143900 0 10/16/2006
123 143900 0 10/17/2006
123 143900 0 10/18/2006
123 143900 32
123 143900 0
123 345534 0 10/3/2006
123 345534 4 10/4/2006
123 345534 0 10/5/2006
123 345534 0 10/6/2006
123 345534 0 10/7/2006
123 345534 0 10/8/2006
123 345534 2 10/9/2006
123 345534 0 10/10/2006
123 345534 0 10/11/2006
123 345534 0 10/12/2006
123 345534 0 10/13/2006
123 345534 0 10/14/2006
123 345534 0 10/15/2006
123 345534 0 10/16/2006
123 345534 0 10/17/2006
123 345534 0 10/18/2006
123 345534 6
123 345534 0
so, for the records where there is a valid sum, it gives the sum, then leaves a blank record and then starts the next employees data. and for employee id 122222, there is no entry for time_inhrs, so its giving only one blank record. and for the last recors that is 345534, its giving the sum (6) first and then leaving the blank record. so the records are not consistent with each other. can this be solved? can we eliminate that blank record after each emplyoees data?
How to summarize data
Devi, February 08, 2007 - 4:33 pm UTC
Hi Tom
We have the following table created for database size growth
create table dbgrowth
(time_stamp date,
db_size number);
insert into dbgrowth values ('01-JUN-2006',50);
insert into dbgrowth values ('14-JUN-2006',54);
insert into dbgrowth values ('28-JUN-2006',56);
insert into dbgrowth values ('01-JUL-2006',58);
insert into dbgrowth values ('14-JUL-2006',60);
insert into dbgrowth values ('28-JUL-2006',64);
insert into dbgrowth values ('01-AUG-2006',68);
insert into dbgrowth values ('14-AUG-2006',70);
insert into dbgrowth values ('28-AUG-2006',72);
insert into dbgrowth values ('01-SEP-2006',74);
insert into dbgrowth values ('14-SEP-2006',76);
insert into dbgrowth values ('28-SEP-2006',78);
commit;
select * from dbgrowth;
TIME_STAMP DB_SIZE
6/1/2006 50
6/14/2006 54
6/28/2006 56
7/1/2006 58
7/14/2006 60
7/28/2006 64
8/1/2006 68
8/14/2006 70
8/28/2006 72
9/1/2006 74
9/14/2006 76
9/28/2006 78
How do we write a query that will provide output summarizing every month the average of the database size.
Example output
June 2006 53
July 2006 60
... and so on
Thank you
Devi
February 08, 2007 - 9:12 pm UTC
select trunc(time_stamp,'mm'), avg(db_size) from dbgrowth group by trunc(time_stamp,'mm')
Thank you
Devi, February 09, 2007 - 10:10 am UTC
Hi Tom
Thank you very much
devi
Tom.. You slipped!!!!!
Jay, May 18, 2007 - 2:36 pm UTC
From your earlier review:
Followup November 24, 2003 - 7am US/Eastern:
well -- there are no "deptno=40" rows!
but anyway, you are doing it "wrong" IMO
IMO............. ???? IM language!!!
Gotcha :-)
Have a good weekend!
use group in union
Sanjay, July 19, 2007 - 2:52 am UTC
hi tom.
I want total bank balance using two tables, hence I wrote.
Select Sum(AccBal) "Total Savings", AccType From tblSavings
Union
Select Sum(AccBal) "Total Balance", AccType From tblCurrent Group By Rollup(AccType);
But it says
ERROR at line 1:
ORA-00937: not a single-group group function
Where am I wrong?
Thanks in Advance.
July 19, 2007 - 10:54 am UTC
ouch... that hurts.
there is a big difference between UNION and UNION ALL - if the two tables had the SAME BALANCE - you would, well, lose one.
You are looking for:
select sum(bal)
from (select sum(accbal) bal from t1
UNION ALL
select sum(accbal) bal from t2)
/
use group in union
Sanjay, July 19, 2007 - 2:52 am UTC
hi tom.
I want total bank balance using two tables, hence I wrote.
Select Sum(AccBal) "Total Savings", AccType From tblSavings
Union
Select Sum(AccBal) "Total Balance", AccType From tblCurrent Group By Rollup(AccType);
But it says
ERROR at line 1:
ORA-00937: not a single-group group function
Where am I wrong?
Thanks in Advance.
Rahul M., July 19, 2007 - 10:58 am UTC
I don't want to open a bank account (or close if i already have) where " Sanjay from India" is working/writing code for..
Just kidding :-)
Ordering of "group by grouping sets" results
ht, November 16, 2007 - 7:22 pm UTC
Tom,
Thank you again for being such a great resource.
I'm running on 10g and would "simply" like the output below to display the "job" before "empnos".
1 select
2 (
3 case when empno is null then job
4 end
5 )job,
6 empno from emp
7* group by grouping sets((job,empno),(job))
>/
7900
7369
7876
7934
CLERK
7788
7902
ANALYST
7566
7698
7782
MANAGER
7499
7521
7654
7844
SALESMAN
7839
PRESIDENT
19 rows selected.
Instead of the above, can you help me with generating this output?
CLERK
7900
7369
7876
7934
ANALYST
7788
7902
MANAGER
7566
7698
7782
SALESMAN
7499
7521
7654
7844
PRESIDENT
7839
November 21, 2007 - 12:29 pm UTC
ops$tkyte%ORA10GR2> select decode( grouping(empno), 1, job_old ) job, empno ,
2 grouping(empno), grouping(job_old)
3 from (select job job_old, empno from scott.emp)
4 group by grouping sets((job_old),(empno,job_old))
5 order by job_old, grouping(empno) DESC
6 /
JOB EMPNO GROUPING(EMPNO) GROUPING(JOB_OLD)
--------- ---------- --------------- -----------------
ANALYST 1 0
7902 0 0
7788 0 0
CLERK 1 0
7900 0 0
7876 0 0
7369 0 0
7934 0 0
MANAGER 1 0
7782 0 0
7566 0 0
7698 0 0
PRESIDENT 1 0
7839 0 0
SALESMAN 1 0
7499 0 0
7521 0 0
7654 0 0
7844 0 0
19 rows selected.
Thanks - will this perform faster than this method?
ht, November 21, 2007 - 2:06 pm UTC
Tom,
Thank you very much for your response. In doing an explain plan, it seems the query below also fits the requirement. It seems they perform well.
Can you comment on the performance aspects of both queries? Would this just be a matter of selecting the query that has a more "intuitive" (yours) syntax?
Thank you again.
SELECT
(
case when row_number() over (partition by deptno order by lower(deptno))=1 then deptno
end
)deptno,
row_number() over (partition by deptno order by deptno,ename) employee#,
ename employees
FROM scott.EMP d
November 21, 2007 - 3:14 pm UTC
well, except for the fact these return entirely different answers
how can I compare them???? think about this please -they return different numbers of rows.
pick the one that returns the right result set.
use that one
do you have a batter way ?
A reader, March 04, 2008 - 3:15 pm UTC
Hi Tom,
I have a table
CREATE TABLE tmpx ( entry_date DATE, item1 NUMBER, item2 NUMBER, iterm3 NUMBER);
INSERT INTO TMPX ( ENTRY_DATE, ITEM1, ITEM2, ITERM3 ) VALUES (
TO_Date( '11/07/2006 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 1, 1);
INSERT INTO TMPX ( ENTRY_DATE, ITEM1, ITEM2, ITERM3 ) VALUES (
TO_Date( '05/28/2007 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 1, 2, 3);
INSERT INTO TMPX ( ENTRY_DATE, ITEM1, ITEM2, ITERM3 ) VALUES (
TO_Date( '01/29/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), NULL, 4, 4);
INSERT INTO TMPX ( ENTRY_DATE, ITEM1, ITEM2, ITERM3 ) VALUES (
TO_Date( '03/03/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 1, 2, 5);
INSERT INTO TMPX ( ENTRY_DATE, ITEM1, ITEM2, ITERM3 ) VALUES (
TO_Date( '03/04/2008 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 5, 8, 9);
COMMIT;
now I want a result like...
date item1 item2 item3
-------------------------------------------
day(today) 5 8 9
week(this week) 6 10 14
month (this month) 6 10 14
year (this Year) 6 14 18
do you have a better way ? using analytics ?
My solution is ....
SELECT r.*
FROM (SELECT p.*,
ROWNUM rfn
FROM (SELECT ROWNUM rn,
q.*
FROM (SELECT TRUNC (entry_date, 'YYYY') y,
TRUNC (entry_date, 'Month') m,
TRUNC (entry_date, 'W') w,
TRUNC (entry_date) d,
SUM (item1),
SUM (item2),
SUM (iterm3)
FROM (SELECT *
FROM tmpx
ORDER BY entry_date DESC)
GROUP BY ROLLUP (TRUNC (entry_date, 'YYYY'),
TRUNC (entry_date, 'Month'),
TRUNC (entry_date, 'W'),
TRUNC (entry_date))) q
WHERE ( d IS NOT NULL
OR w IS NOT NULL
OR m IS NOT NULL
OR y IS NOT NULL
)
ORDER BY rn DESC) p) r
where rfn < 5;
March 04, 2008 - 6:01 pm UTC
ops$tkyte%ORA10GR2> with data as (select level r from dual connect by level <= 4)
2 select decode( r, 1, 'day', 2, 'week', 3, 'month', 4, 'year' ) dt,
3 decode( r, 1, dy_i1, 2, wk_i1, 3, mm_i1, 4, yr_i1 ) i1,
4 decode( r, 1, dy_i2, 2, wk_i2, 3, mm_i2, 4, yr_i2 ) i2,
5 decode( r, 1, dy_i3, 2, wk_i3, 3, mm_i3, 4, yr_i3 ) i3
6 from (
7 select sum( case when trunc(entry_date,'dd') = trunc(sysdate,'dd') then item1 end ) dy_i1,
8 sum( case when trunc(entry_date,'dd') = trunc(sysdate,'dd') then item2 end ) dy_i2,
9 sum( case when trunc(entry_date,'dd') = trunc(sysdate,'dd') then item3 end ) dy_i3,
10 sum( case when trunc(entry_date,'w') = trunc(sysdate,'w') then item1 end ) wk_i1,
11 sum( case when trunc(entry_date,'w') = trunc(sysdate,'w') then item2 end ) wk_i2,
12 sum( case when trunc(entry_date,'w') = trunc(sysdate,'w') then item3 end ) wk_i3,
13 sum( case when trunc(entry_date,'mm') = trunc(sysdate,'mm') then item1 end ) mm_i1,
14 sum( case when trunc(entry_date,'mm') = trunc(sysdate,'mm') then item2 end ) mm_i2,
15 sum( case when trunc(entry_date,'mm') = trunc(sysdate,'mm') then item3 end ) mm_i3,
16 sum( case when trunc(entry_date,'yyyy') = trunc(sysdate,'yyyy') then item1 end ) yr_i1,
17 sum( case when trunc(entry_date,'yyyy') = trunc(sysdate,'yyyy') then item2 end ) yr_i2,
18 sum( case when trunc(entry_date,'yyyy') = trunc(sysdate,'yyyy') then item3 end ) yr_i3
19 from tmpx
20 ), data;
DT I1 I2 I3
----- ---------- ---------- ----------
day 5 8 9
week 6 10 14
month 6 10 14
year 6 14 18
nice!
A reader, March 05, 2008 - 10:43 am UTC
This is cool but I didn't understand it fully!
Can you explain it a little bit , not the code but the concept and how did you come to each conclusion on each step ?
March 05, 2008 - 3:02 pm UTC
7 select sum( case when trunc(entry_date,'dd') = trunc(sysdate,'dd') then item1 end ) dy_i1,
8 sum( case when trunc(entry_date,'dd') = trunc(sysdate,'dd') then item2 end ) dy_i2,
9 sum( case when trunc(entry_date,'dd') = trunc(sysdate,'dd') then item3 end ) dy_i3,
10 sum( case when trunc(entry_date,'w') = trunc(sysdate,'w') then item1 end ) wk_i1,
11 sum( case when trunc(entry_date,'w') = trunc(sysdate,'w') then item2 end ) wk_i2,
12 sum( case when trunc(entry_date,'w') = trunc(sysdate,'w') then item3 end ) wk_i3,
13 sum( case when trunc(entry_date,'mm') = trunc(sysdate,'mm') then item1 end ) mm_i1,
14 sum( case when trunc(entry_date,'mm') = trunc(sysdate,'mm') then item2 end ) mm_i2,
15 sum( case when trunc(entry_date,'mm') = trunc(sysdate,'mm') then item3 end ) mm_i3,
16 sum( case when trunc(entry_date,'yyyy') = trunc(sysdate,'yyyy') then item1 end ) yr_i1,
17 sum( case when trunc(entry_date,'yyyy') = trunc(sysdate,'yyyy') then item2 end ) yr_i2,
18 sum( case when trunc(entry_date,'yyyy') = trunc(sysdate,'yyyy') then item3 end ) yr_i3
19 from tmpx
that bit is very standard - you wanted sums for different conditions - just put the condition in a case and sum them up. that part shouldn't be too obtuse.
the other bit was just a typical "unpivot", turning columns into rows, something I do a lot - just a standard technique.
the how - I skipped reading your query entirely and just looked at the inputs/outputs. Even though you were not verbose, I was able to figure out the question and from there - just needed the data.
knowing how to sum with a "where clause" using case
and knowing how to unpivot
leads to "quite easily done"
Thank you!
A reader, March 05, 2008 - 4:26 pm UTC
Strange goings-on with the grand total in ROLLUP
Joe Bloggs, July 25, 2008 - 6:01 am UTC
Dear Tom,
I have strange goings-on with the grand total when I execute the below query. For some reason I cannot fathom, the
grand totals for columns INIT_IMPACT_TOTAL_COST and RENEW_IMPACT_TOTAL_COST always stay blank - despite their intermediary sub-totals above being correctly filled in.
Strangely enough, NO_OF_ROWS (a simple COUNT(*) column)
is counted correctly in the GT, but the other 2 columns
being (COUNT(*) * <something>) are not.
I tried similar coding using GROUPING SETS ((a,b,c), ()) etc. to try displaying the GT, but it doesn't come back with anything either !
Have I hit on a bug with ROLLUP (and/or GROUPING SETS), or have I mis-understood some subtlety in its use ?
Code is below, and sample output below that.
WITH MAIN AS
(SELECT TEMP_DATAMART.*,
COUNT(*) OVER (PARTITION BY NAME_AND_ADDRESS_MATCH,
CARD_MATCH,
BANK_MATCH,
PRODUCT_APPLIED_FOR,
EXISTING_PRODUCT,
CLIENT,
MEDIA,
INIT_PRICE_POINT,
RENEW_PRICE_POINT) AS MATCH_METHOD_COUNT
FROM TEMP_DATAMART
WHERE EXISTING_PACKAGE_TYPE = APPLIED_FOR_PACKAGE_TYPE
AND EXISTING_PRODUCT_TYPE = 'Retail' AND NEW_PRODUCT_TYPE = 'Retail')
--
--
SELECT NAME_AND_ADDRESS_MATCH,
CARD_MATCH,
DECODE (GROUPING(BANK_MATCH), 1, 'SUMMARY', BANK_MATCH) AS SUMMARY_BANK_MATCH,
PRODUCT_APPLIED_FOR,
EXISTING_PRODUCT,
CLIENT,
MEDIA,
INIT_PRICE_POINT,
RENEW_PRICE_POINT,
EXISTING_PRODUCT_TYPE_MEMBERID,
NEW_PRODUCT_TYPE_MEMBER_ID,
EXISTING_PRODTYP_MEMBERID_DATE,
NEW_PRODUCT_TYPE_MEMBERID_DATE,
MATCH_METHOD_COUNT,
COUNT(*) AS NO_OF_ROWS, <-- Displayed in GT
(INIT_PRICE_POINT * COUNT (*)) AS INIT_IMPACT_TOTAL_COST, <-- NOT displayed in GT
(RENEW_PRICE_POINT * COUNT (*)) AS RENEW_IMPACT_TOTAL_COST <-- NOT displayed in GT
FROM MAIN
GROUP BY ROLLUP ((PRODUCT_APPLIED_FOR,
EXISTING_PRODUCT,
CLIENT,
MEDIA,
INIT_PRICE_POINT,
RENEW_PRICE_POINT),
(NAME_AND_ADDRESS_MATCH,
CARD_MATCH,
BANK_MATCH,
EXISTING_PRODUCT_TYPE_MEMBERID,
NEW_PRODUCT_TYPE_MEMBER_ID,
EXISTING_PRODTYP_MEMBERID_DATE,
NEW_PRODUCT_TYPE_MEMBERID_DATE,
MATCH_METHOD_COUNT))
ORDER BY PRODUCT_APPLIED_FOR,
EXISTING_PRODUCT,
CLIENT,
MEDIA,
INIT_PRICE_POINT,
RENEW_PRICE_POINT,
NAME_AND_ADDRESS_MATCH,
CARD_MATCH,
DECODE (GROUPING(BANK_MATCH), 1, 3, DECODE (BANK_MATCH, 'X', 1, 2)),
EXISTING_PRODUCT_TYPE_MEMBERID,
NEW_PRODUCT_TYPE_MEMBER_ID,
EXISTING_PRODTYP_MEMBERID_DATE,
NEW_PRODUCT_TYPE_MEMBERID_DATESample Output (manually doctored due to space, but accurate):
NO_OF_ROWS INIT_IMPACT_TOTAL_COST RENEW_IMPACT_TOTAL_COST
1 69.99 69.99
1 69.99 69.99
1 69.99 69.99
1 69.99 69.99
1 69.99 69.99
1 69.99 69.99
1 69.99 69.99
1 69.99 69.99
4 279.96 279.96
1 6.99 6.99
1 6.99 6.99
1 6.99 6.99
1 6.99 6.99
1 6.99 6.99
1 6.99 6.99
1 6.99 6.99
5 34.95 34.95
1 6.99 6.99
1 6.99 6.99
1 6.99 6.99
3 20.97 20.97
3063
^ ^
¦ ¦
No figures for the GT for these 2 columns
July 29, 2008 - 10:14 am UTC
I'd need an example to play with. create table, inserts, etc.
Joe Bloggs, July 29, 2008 - 11:16 am UTC
I have just taken an extract of the data and re-run it on a different machine, and I still get the same results.
Can I email you the file since there are 100 records to INSERT.
July 29, 2008 - 4:08 pm UTC
sure, thomas.kyte@oracle.com - use a plain text file - include create table, insert into and offending query - along with an explanation of what you expected to see...
another rollup ???
Peter S., July 29, 2008 - 4:04 pm UTC
Hi Tom, I need some statements, but they can broke down very easy to one table with two columns...
create table TEST
(
country varchar2 (100)
companyname varchar2 (100)
)
how can I EASY create an query to get all companynames from all countries, which countries have more than X (100) companies...
to make it clear, an example: all companies from countries, which have exactly two companies...
so if SPAIN has 3 companies, I don't want to know the companies of spain... but if GERMANY has two companys, I need to know them...
I'm sure it's possible to write it in one easy statement, I just can't find it... now I group by the country with having count(*) >100 and then I join it with the table TEST again... but since I must join some tables to get my base-table, I want to know the best way...
thanks Tom !!!
August 01, 2008 - 10:30 am UTC
ops$tkyte%ORA10GR2> create table t
2 (
3 country varchar2 (20),
4 companyname varchar2 (20)
5 )
6 /
Table created.
ops$tkyte%ORA10GR2> insert into t
2 select case when rownum <= 5 then 'US' else 'GERMANY' end, 'company ' || rownum
3 from all_users
4 where rownum <= 11;
11 rows created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select country, companyname, count(*) over (partition by country) cnt
2 from t
3 /
COUNTRY COMPANYNAME CNT
-------------------- -------------------- ----------
GERMANY company 6 6
GERMANY company 10 6
GERMANY company 9 6
GERMANY company 8 6
GERMANY company 7 6
GERMANY company 11 6
US company 5 5
US company 4 5
US company 3 5
US company 2 5
US company 1 5
11 rows selected.
ops$tkyte%ORA10GR2> select * from (
2 select country, companyname, count(*) over (partition by country) cnt
3 from t
4 ) where cnt <= 5
5 /
COUNTRY COMPANYNAME CNT
-------------------- -------------------- ----------
US company 5 5
US company 4 5
US company 3 5
US company 2 5
US company 1 5
Joe Bloggs, July 30, 2008 - 8:19 am UTC
SELECT CompanyName
FROM TEST
GROUP BY CompanyName
HAVING COUNT(*) > 100
thanks joe, but not exactly what I need...
peter S., July 30, 2008 - 2:04 pm UTC
what I need is somethink like GROUP BY country , but SELECT CompanyName...
sampleData:
Country CompanyName
DE DE1
DE DE2
CH CH1
CH CH2
CH CH3
FR FR1
AUT AUT1
AUT AUT2
so I want to get the ALL COMPANIES, but only from DE and AUT, since the have exactly two companies...
result:
Country CompanyName
DE DE1
DE DE2
AUT AUT1
AUT AUT2
thanks for the fast reply !!!
Try this
rsergio, July 30, 2008 - 4:37 pm UTC
select companyname
from test
where country in (
select country
from (select country, count(*)
from test
group by country
having count(*) > 100))
wow, cool answer rsergio !
Peter S., July 30, 2008 - 5:33 pm UTC
bevore I wrote it like this:
select companyname, test.country
from test,
( select country
from test
group by country
having count(*) > 100
) countries_100
where test.country = countries_100.country
but I asked myself how to do this statement with only ONE select ? as said, I must join more tables and i think the calculation on the table test is done twice, but maybe it's only my feeling... Is there an command in the Rollup or group by for this cases ?
p.S.: is it nescessary to do
-- select country
-- from (select country, ...
? so without this having will not work "right" - for this question - as I read above ?
It's not necessary
rsergio, July 30, 2008 - 7:50 pm UTC
It's better like this:
select companyname
from test
where country in (
select country
from test
group by country
having count(*) > 100)
August 02, 2008 - 5:44 pm UTC
I'm fond of this approach:
ops$tkyte%ORA10GR2> select * from (
2 select country, companyname, count(*) over (partition by country) cnt
3 from t
4 ) where cnt <= 5
5 /
we can avoid hitting the table two times that way.
wow tom, you now everything...
Peter S., August 01, 2008 - 1:34 pm UTC
thanks so much, I know that there is an better solution than mine...
Question in Rollup
Raj, October 15, 2008 - 10:12 am UTC
I have a scenario like this..
I want the result set in such a manner that if the deptno = 10 then it should rollup the salary up to Manager level(i.e., President,Manager).
In all other cases it should rollup to all the level(i.e., Manager,Analyst,Clerk).
CREATE TABLE EMP
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7566, 'JONES', 'MANAGER', 7839, TO_Date( '04/02/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2975, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7788, 'SCOTT', 'ANALYST', 7566, TO_Date( '12/09/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7902, 'FORD', 'ANALYST', 7566, TO_Date( '12/03/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 3000, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7876, 'ADAMS', 'CLERK', 7788, TO_Date( '01/12/1983 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1100, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7369, 'SMITH', 'CLERK', 7902, TO_Date( '12/17/1980 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 800, NULL, 20);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7934, 'MILLER', 'CLERK', 7782, TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 1300, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7839, 'KING', 'PRESIDENT', NULL, TO_Date( '11/17/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 5000, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES (
7782, 'CLARK', 'MANAGER', 7839, TO_Date( '06/09/1981 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 2450, NULL, 10);
COMMIT;
Row# EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 7782 CLARK MANAGER 7839 6/9/1981 2450 10
2 7839 KING PRESIDENT 11/17/1981 5000 10
3 7934 MILLER CLERK 7782 1/23/1982 1300 10
4 7369 SMITH CLERK 7902 12/17/1980 800 20
5 7876 ADAMS CLERK 7788 1/12/1983 1100 20
6 7902 FORD ANALYST 7566 12/3/1981 3000 20
7 7788 SCOTT ANALYST 7566 12/9/1982 3000 20
8 7566 JONES MANAGER 7839 4/2/1981 2975 20
Expected Result set
DEPTNO SAL
10 7450
20 10875
October 15, 2008 - 5:34 pm UTC
ops$tkyte%ORA11GR1> select deptno,
2 sum( case when deptno = 10 and job in ('PRESIDENT','MANAGER') then sal
3 when nvl(deptno,11) <> 10 then sal
4 end ) sumsal
5 from scott.emp
6 group by deptno;
DEPTNO SUMSAL
---------- ----------
30 9400
20 10875
10 7450
not really anything to do with ROLLUP (which is a sql thing), but an aggregate.
ROLLUP AND CUBE
Raj, October 16, 2008 - 9:50 am UTC
Thanks Tom.
Variation using MODEL
Peter Kinsella, October 17, 2008 - 9:15 am UTC
select deptno, sum(newsal)
from
(select deptno, job, newsal
from emp
model ignore nav
partition by (empno)
dimension by (deptno, job)
measures (sal newsal)
rules(
newsal[10, job in ('ANALYST', 'CLERK')] = 0
)
)
group by deptno
order by deptno
/
How to use it to get sub-totals
H, September 05, 2011 - 2:19 pm UTC
Hello Sir,
I m using this query
1 select job,deptno, count(*)total
2 from emp
3 group by deptno,grouping sets(job)
4* order by deptno
SQL> /
JOB DEPTNO TOTAL
--------- ---------- ----------
CLERK 10 1
MANAGER 10 1
PRESIDENT 10 1
ANALYST 20 2
CLERK 20 2
MANAGER 20 1
CLERK 30 1
MANAGER 30 1
SALESMAN 30 4
9 rows selected.
The rsult I want should be like this
JOB DEPTNO TOTAL
--------- ---------- ----------
CLERK 10 1
MANAGER 10 1
PRESIDENT 10 1
Total 3
ANALYST 20 2
CLERK 20 2
MANAGER 20 1
Total 5
CLERK 30 1
MANAGER 30 1
SALESMAN 30 4
Total 6
grand total 14
How do I do that?
Thanks
September 06, 2011 - 10:43 am UTC
ops$tkyte%ORA11GR2> select decode( grouping(job),1,decode(grouping(deptno), 1, 'grand total', 'total'),job) job ,deptno, count(*)total, grouping(deptno), grouping( job )
2 from scott.emp
3 group by grouping sets (( deptno, job ), (deptno), ())
4 /
JOB DEPTNO TOTAL GROUPING(DEPTNO) GROUPING(JOB)
----------- ---------- ---------- ---------------- -------------
CLERK 10 1 0 0
MANAGER 10 1 0 0
PRESIDENT 10 1 0 0
total 10 3 0 1
CLERK 20 2 0 0
ANALYST 20 2 0 0
MANAGER 20 1 0 0
total 20 5 0 1
CLERK 30 1 0 0
MANAGER 30 1 0 0
SALESMAN 30 4 0 0
total 30 6 0 1
grand total 14 1 1
13 rows selected.
Thanks a lot
A reader, September 06, 2011 - 12:49 pm UTC
Sir thank you for your reply. you have always been very helpful
Multilevel aggregation or something else
Ajeet, April 20, 2012 - 10:00 am UTC
Hi Tom,
I will provide the create tables and test data for this question but before that I want to ask that what is the best way to achieve my requirments in oracle.
I have 8 fields in a search screen , they are like drop down lists. user can choose any possible combinations of these 8 fields as search option and then once select -application will display the results, these results basically do grouping on the selected fields in the search screen. there is another column "value" which will be grouped by the columns/fieds as choosen by customer.
so for example : i have a table
create table t(col1, col2, col3,col4,.....,value) ;
and now user can select either col1 ,or col1,col2 or col1,col3 or col1,col2,col8 ..and so on and then I am supposed to write a query which will return me
either select col1,col2, sum(values) group by col1, col2 or col1,col2,col8,sum(values) group by col1,col2,col8 ..so on.
I undersatnd I can use CUBE to do this.
My plan is to populate another table which will have data from the output of query with CUBE.
My question is how would I identify which row belongs to which category..i mean which row is due to col1,col2,sum(values) and which one is col1,col2,col8,sum(values) ?
second question is there a better way to achieve this goal.
I need to create an intermediate table date in the base table is very huge ( say 100 millions rows ) and the serach results should display results in less than 3 seconds. so I though I should create an intermediate table.
the problem is how would I identify that which row reprsents what level of aggregate info.
how can I remove the unwanted rows from this table (eg. - sum at all the levels ,I don;t need it).
Thanks
April 22, 2012 - 8:54 am UTC
and so on and then I am supposed to write a query which will
return me
either select col1,col2, sum(values) group by col1, col2 or
col1,col2,col8,sum(values) group by col1,col2,col8 ..so on.
I undersatnd I can use CUBE to do this.
why would you need cube. according to what you wrote, all you need is either:
select col1, col2, sum(values) from t group by col1, col2;
or
select col1, col2, col8, sum(values) from t group by col1, col2, col8;
You didn't write anything that indicates you need anything more? You would use dynamic sql, build a query that selects and groups by the columns in question and you are done.
You are probably not going to full scan 100's of millions of rows in less than three seconds.
You'll need to be much more clear here, I don't see anything "fancy" here at all.
Dynamic sql- rechcek
A reader, April 22, 2012 - 9:53 am UTC
Hi Tom,
Thnak you for you answer above.
Yes dyanamic sql is the option I would also like to use.a
the only reason,I was thinking about using CUBE or rollup because user can choose any possible combination of the 8 search fields so I was thinking that is CUBE a better option.
I am planing to write dynamic SQL using ref cursor which will cater to this requirement. I am thinking that whether i should write a dynamic sql which will handle all the scienarios or should i write if-then-else and then using ref cusror handle each and every possible scianrio as seperate SQL.
I'll try both but want to understnad what would be the better approach.
and yes the data volume in the tables will be few thousands rows maximum.
Thanks
April 22, 2012 - 10:32 am UTC
cube would not be better - you'd have to either
a) use dynamic sql to pick just the dimensions you wanted to rollup by - so you'd be back to dynamic sql
b) generate the ENTIRE cube and procedurally pick off the rows you wanted - this would be beyond "not smart"
You just need group by - sort the column names alphabetically and you'll generate the smallest number of sql statements (eg: don't generate:
select c1, c2, ...
and
select c2, c1, ....
just generate
select c1, c2, ....
extermely useful insight
Ajeet, April 23, 2012 - 2:24 am UTC
Hi Tom,
Thanks for such wonderful suggestion.I'll develope the dyanamic sql.
Regards
one more question
Ajeet, April 23, 2012 - 3:47 am UTC
Hi Tom,
More I think on the above issue , more confused I am.
I guess if user will select a value from the serach screen for a column say col1 = 'abcz' and then hit the button the get the results. or he choose col1= 'abcz' and col2='xyz' and col3= 1234 then would it not be ok to store just
select c1 ,c2,c3,c4,c5,c6,c7,c8 ,sum(val)
group by c1,c2,c3,c4,c5,c6,c7,c8 and then apply a filter
c1 = 'abcz' and c2='xyz' and c2=1234 ;
and if they choose more filters then then append those filters in the where clause ..will it be incorrect to do.
as in the search screen basically user can select a value for any combinations of 8 fields as I mentioned above.
just tyin to think hard and sometime I end up doing lot of work though the actual requirement is simple.
Thanks for your patience to listen our problems.
April 23, 2012 - 1:25 pm UTC
read this:
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html to see how to properly construct a dynamic ref cursor using bind variables, that'll be VITAL and you MUST DO THAT (to avoid sql injection and be shared pool friendly)
As for "would it be ok to store just ...."
I don't know, you tell me - if they select c1,c2,c3 - do you want to filter just by c1,c2,c3 and then aggregate by c1..c8?
Only you know the answer to that - right....
Thanks for the link
Ajeet, April 25, 2012 - 2:31 am UTC
Hi Tom,
Thanks for the link to the code which handles dynamic where clauses. I have seen that before and used it also.
one thing which is a bit not clear (lack of my understanding) , in one of the above post you have suggested to sort the columns in order to take the minimum possible combinations ,if we need to do a dynamic group by columns.
how the sorting will help ,any small exmaple which can explain it a bit ...without taking your too much of time will help us learn something new,
Thanks
April 25, 2012 - 9:44 am UTC
the sorting of the columns alphabetically is so you create a predictable query - if the user picked c1, c5, c10 - you would generate:
select c1, c5, c10, .....
and NOT
select c5, c10, c1
select c10,c5,c1
select c1,c10,c5
......
you would minimize the set of sql's you would generate by always putting the column names in the same order.
very useful
Ajeet, April 26, 2012 - 1:38 am UTC
Thanks Tom, extermely useful advice.
Regards
what indexes we can create
Ajeet, April 26, 2012 - 9:34 am UTC
Hi Tom
on the above issue, now I am now able to test the code. the one thing which I want to ask here is now below is my table struture.
SQL>desc test
Name
--------------------------------
APP_NAME
LEGACY_BUSINESS
LEGACY_SUB_BUSINESS
LEGACY_PROFIT_AND_LOSS_CENTER
POLE
LOCATION
CI_TYPE
CI_CLASS
APP_CONTAINS_EC_DATA
WORKGROUP
CRITICALITY
ROBOT
SAMPLE_DATE
RUNS
SUCCESS
COMPROMISED
RESP_TIME
PERF
AVAIL
METRIC_TYPE
CREATION_DATE
the serach program which usage the dynamic sql code which you suggested can search the data by any permutation ,combination of fields in the table except the fields perf,avail ,and creation date.
I was wonedering what all indexes I should create on this table in order to get a better performance.
any advice will let me think in the righ direction.
Regards
Ajeet
April 26, 2012 - 9:43 am UTC
is this data read only or read mostly (loaded periodically) or is it constantly modified.
read only
Ajeet, April 26, 2012 - 11:19 pm UTC
Hi Tom,
Data in the table is read only, it will be loaded every Sunday only using a PL/SQL procedure - which will do insert Append .no other updates or insert. Just select queries which will come from the dynamic SQL procedure.
Regards
Ajeet
April 27, 2012 - 7:56 am UTC
then I would suggest benchmarking with a set of single column bitmap indexes on the non-selective columns you anticipate being most frequently used in predicates togehter.
In that fashion, we can combine indices together at run time.
and then conventional b*tree indices if you want on the very selective columns - if they are used in the where clause, you only need the b*tree, you don't need to combine them
For example:
ops$tkyte%ORA11GR2> create table t
2 ( gender not null,
3 location not null,
4 age_group not null,
5 data
6 )
7 as
8 select decode( ceil(dbms_random.value(1,2)),
9 1, 'M',
10 2, 'F' ) gender,
11 ceil(dbms_random.value(1,50)) location,
12 decode( ceil(dbms_random.value(1,5)),
13 1,'18 and under',
14 2,'19-25',
15 3,'26-30',
16 4,'31-40',
17 5,'41 and over'),
18 rpad( '*', 20, '*')
19 from (select rownum from stage union all select rownum from stage)
20 /
Table created.
we have a table with lots of "demographic" information and would like to ad-hoc query it by various columns
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> create bitmap index gender_idx on t(gender);
Index created.
ops$tkyte%ORA11GR2> create bitmap index location_idx on t(location);
Index created.
ops$tkyte%ORA11GR2> create bitmap index age_group_idx on t(age_group);
Index created.
we'll discover that we can use multiple bitmaps to merge together into a single one
ops$tkyte%ORA11GR2> explain plan for
2 select count(*)
3 from T
4 where gender = 'M'
5 and location in ( 1, 10, 30 )
6 and age_group = '41 and over';
Explained.
ops$tkyte%ORA11GR2> pause
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1811480857
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 5
| 1 | SORT AGGREGATE | | 1 | 13 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 13 | 5
| 3 | BITMAP AND | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | GENDER_IDX | | |
| 5 | BITMAP OR | | | |
|* 6 | BITMAP INDEX SINGLE VALUE| LOCATION_IDX | | |
|* 7 | BITMAP INDEX SINGLE VALUE| LOCATION_IDX | | |
|* 8 | BITMAP INDEX SINGLE VALUE| LOCATION_IDX | | |
|* 9 | BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX | | |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("GENDER"='M')
6 - access("LOCATION"=1)
7 - access("LOCATION"=10)
8 - access("LOCATION"=30)
9 - access("AGE_GROUP"='41 and over')
25 rows selected.
to count those rows - we just used the indexes and combined them
ops$tkyte%ORA11GR2> explain plan for
2 select *
3 from t
4 where ( ( gender = 'M' and location = 20 )
5 or ( gender = 'F' and location = 22 ))
6 and age_group = '18 and under';
Explained.
ops$tkyte%ORA11GR2> pause
ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 906765108
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 742 | 25228 | 109
| 1 | TABLE ACCESS BY INDEX ROWID | T | 742 | 25228 | 109
| 2 | BITMAP CONVERSION TO ROWIDS | | | |
| 3 | BITMAP AND | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | AGE_GROUP_IDX | | |
| 5 | BITMAP OR | | | |
| 6 | BITMAP AND | | | |
|* 7 | BITMAP INDEX SINGLE VALUE| LOCATION_IDX | | |
|* 8 | BITMAP INDEX SINGLE VALUE| GENDER_IDX | | |
| 9 | BITMAP AND | | | |
|* 10 | BITMAP INDEX SINGLE VALUE| GENDER_IDX | | |
|* 11 | BITMAP INDEX SINGLE VALUE| LOCATION_IDX | | |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("AGE_GROUP"='18 and under')
7 - access("LOCATION"=22)
8 - access("GENDER"='F')
10 - access("GENDER"='M')
11 - access("LOCATION"=20)
27 rows selected.
to get those rows, we just merge the indices together and converted bitmaps to rowids
so, I would suggest benchmarking those
(also, enable compression on the table before direct path loading!)
thanks
Ajeet, April 27, 2012 - 10:07 am UTC
Hi Tom,
Thanks so much for your time and suggestin, I did create bitmap indexes and can see good results , almost all queries returns the results in few seconds. I'll test more and post updates.
Thanks !
Ajeet
Getting the 84th percentile of each grouping
rebisco, October 07, 2012 - 11:16 pm UTC
Hi Tom,
I have the following requirement where I was asked to compute the 84th percentile of a given data. 84th percentile in this case is the value of AVGY after accumulating the LCQ of each group (84 <= accumulated LCQ of each group). Each row will be grouped according to PSTAGE, PGROUP, PLGROUP, PPLAN PCODE, LTYPE and LID. To get the accumulated LCQ, we will use the following formula:
accumulated LCQ = SUM(LCQ*100/Total LCQ of each group)
PSTAGE PGROUP PLGROUP PPLAN PCODE LTYPE LID LCQ AVGY 84TH GROUPING
STAGE1 GROUP1 PG1 PLAN1 CODE1 P 111 1626 96.156 ?
STAGE1 GROUP2 PG2 PLAN2 CODE2 P 112 1000 100.000 ?
STAGE1 GROUP2 PG3 PLAN3 CODE3 P 113 1000 100.000 ?
STAGE1 GROUP2 PG4 PLAN4 CODE2 P 114 1000 100.000 ?
STAGE2 GROUP3 PG5 PLAN5 CODE4 P 115 28361 97.427 ?
STAGE2 GROUP3 PG6 PLAN6 CODE5 P 116 500 100.000 ?
STAGE3 GROUP4 PG7 PLAN7 CODE6 P 117 5000 99.681 ?
STAGE3 GROUP4 PG7 PLAN7 CODE6 P 118 0 0.000 ?
STAGE3 GROUP4 PG8 PLAN8 CODE7 P 119 7200 136.648 ?
STAGE3 GROUP4 PG9 PLAN9 CODE8 P 120 800 100.756 ?
STAGE3 GROUP4 PG9 PLAN9 CODE8 P 121 0 0.000 ?
For example, to get the 84TH percentile and Grouping of STAGE3(84TH and GROUPING columns are generated):
PSTAGE PGROUP PLGROUP PPLAN PCODE LTYPE LID LCQ AVGY 84TH GROUPING
STAGE3 GROUP4 PG7 PLAN7 CODE6 P 117 5000 99.68 0
STAGE3 GROUP4 PG7 PLAN7 CODE6 P 118 0 0 0
STAGE3 GROUP4 PG7 PLAN7 CODE6 P 5000 99.13 99.68 01.LTYPE
STAGE3 GROUP4 PG7 PLAN7 CODE6 5000 99.13 99.68 02.PCODE
STAGE3 GROUP4 PG7 PLAN7 5000 99.13 99.68 03.PPLAN
STAGE3 GROUP4 PG7 5000 99.13 99.68 04.PLGROUP
STAGE3 GROUP4 PG8 PLAN8 CODE7 P 119 7200 136.65 0
STAGE3 GROUP4 PG8 PLAN8 CODE7 P 7200 136.65 136.65 01.LTYPE
STAGE3 GROUP4 PG8 PLAN8 CODE7 7200 136.65 136.65 02.PCODE
STAGE3 GROUP4 PG8 PLAN8 7200 136.65 136.65 03.PPLAN
STAGE3 GROUP4 PG8 7200 136.65 136.65 04.PLGROUP
STAGE3 GROUP4 PG9 PLAN9 CODE8 P 120 800 100.76 0
STAGE3 GROUP4 PG9 PLAN9 CODE8 P 121 0 0 0
STAGE3 GROUP4 PG9 PLAN9 CODE8 P 800 99.5 100.76 01.LTYPE
STAGE3 GROUP4 PG9 PLAN9 CODE8 800 99.5 100.76 02.PCODE
STAGE3 GROUP4 PG9 PLAN9 800 99.5 100.76 03.PPLAN
STAGE3 GROUP4 PG9 800 99.5 100.76 04.PLGROUP
STAGE3 GROUP4 13000 116.94 99.68 05.PGROUP
STAGE3 13000 116.94 99.68 06.PSTAGE
I tried the GROUP BY ROLLUP (PSTAGE, PGROUP, PLGROUP, PPLAN PCODE, LTYPE and LID) to get the subtotal of each grouping but, I failed to get the 84TH percentile of each grouping. I just dont know how to contruct the right SQL for this.
Table create/insert:
SQL> CREATE TABLE TEST1 AS
2 SELECT 'STAGE1' PSTAGE, 'GROUP1' PGROUP, 'PG1' PLGROUP, 'PLAN1' PPLAN, 'CODE1' PCODE, 'P' LTYPE, 111 LID, 1626 LCQ, 96.156 AVGY FROM DUAL
3 UNION ALL
4 SELECT 'STAGE1' PSTAGE, 'GROUP2' PGROUP, 'PG2' PLGROUP, 'PLAN2' PPLAN, 'CODE2' PCODE, 'P' LTYPE, 112 LID, 1000 LCQ, 100 AVGY FROM DUAL
5 UNION ALL
6 SELECT 'STAGE1' PSTAGE, 'GROUP2' PGROUP, 'PG3' PLGROUP, 'PLAN3' PPLAN, 'CODE3' PCODE, 'P' LTYPE, 113 LID, 1000 LCQ, 100 AVGY FROM DUAL
7 UNION ALL
8 SELECT 'STAGE1' PSTAGE, 'GROUP2' PGROUP, 'PG4' PLGROUP, 'PLAN4' PPLAN, 'CODE2' PCODE, 'P' LTYPE, 114 LID, 1000 LCQ, 100 AVGY FROM DUAL
9 UNION ALL
10 SELECT 'STAGE2' PSTAGE, 'GROUP3' PGROUP, 'PG5' PLGROUP, 'PLAN5' PPLAN, 'CODE4' PCODE, 'P' LTYPE, 115 LID, 28361 LCQ, 97.427 AVGY FROM DUAL
11 UNION ALL
12 SELECT 'STAGE2' PSTAGE, 'GROUP3' PGROUP, 'PG6' PLGROUP, 'PLAN6' PPLAN, 'CODE5' PCODE, 'P' LTYPE, 116 LID, 500 LCQ, 100 AVGY FROM DUAL
13 UNION ALL
14 SELECT 'STAGE3' PSTAGE, 'GROUP4' PGROUP, 'PG7' PLGROUP, 'PLAN7' PPLAN, 'CODE6' PCODE, 'P' LTYPE, 117 LID, 5000 LCQ, 99.681 AVGY FROM DUAL
15 UNION ALL
16 SELECT 'STAGE3' PSTAGE, 'GROUP4' PGROUP, 'PG7' PLGROUP, 'PLAN7' PPLAN, 'CODE6' PCODE, 'P' LTYPE, 118 LID, 0 LCQ, 0 AVGY FROM DUAL
17 UNION ALL
18 SELECT 'STAGE3' PSTAGE, 'GROUP4' PGROUP, 'PG8' PLGROUP, 'PLAN8' PPLAN, 'CODE7' PCODE, 'P' LTYPE, 119 LID, 7200 LCQ, 136.648 AVGY FROM DUAL
19 UNION ALL
20 SELECT 'STAGE3' PSTAGE, 'GROUP4' PGROUP, 'PG9' PLGROUP, 'PLAN9' PPLAN, 'CODE8' PCODE, 'P' LTYPE, 120 LID, 800 LCQ, 100.756 AVGY FROM DUAL
21 UNION ALL
22 SELECT 'STAGE3' PSTAGE, 'GROUP4' PGROUP, 'PG9' PLGROUP, 'PLAN9' PPLAN, 'CODE8' PCODE, 'P' LTYPE, 121 LID, 0 LCQ, 0 AVGY FROM DUAL
23 ;
Table created
Thank you again Tom for this great website. It has been a great help for us.
One more requirement
rebisco, October 07, 2012 - 11:23 pm UTC
Hello Tom,
I forgot to mention one of the requirements. Each grouping will be sorted by AVGY in descending order.
Thanks again,
Rebisco
October 09, 2012 - 12:37 pm UTC
sorry, but I understood nothing in the above. You do know that your acronyms are meaningless to us and a picture of a report doesn't tell anyone what the requirements of the report are right? I mean - I have no idea what you are looking for truly.
Specification
rebisco, October 09, 2012 - 8:51 pm UTC
Sorry about that. Anyways, they are just column names and nothing to do with the computation. Here is an example but, it is only for one grouping (PCODE):
SQL> select * from test;
PCODE LID LCQ AVGY
------ ----- ----- ----
prod1 lot1 2000 98
prod1 lot2 1700 92
prod1 lot3 5000 96
prod1 lot4 300 80
prod1 lot5 1000 85
But, since the original requirement states that we need to sort by AVGY in descending order, we get:
PCODE LID LCQ AVGY
------ ----- ----- ----
prod1 lot1 2000 98
prod1 lot3 5000 96
prod1 lot2 1700 92 <------84th percentile
prod1 lot5 1000 85
prod1 lot4 300 80
Expected 84th percentile in the above data is 92 since,
accumulated LCQ = (2000*100/10000) + (5000*100/10000) + (1700*100/10000) = 87
which is the minimum value of accumulated LCQ after reaching the value 84.
To arrive the above conclusion I run the following query:
SQL> SELECT PCODE, LCQ, AVGY
2 FROM
3 (
4 SELECT
5 a.*,
6 SUM(LCQ*100/totLCQ) over (PARTITION BY PCODE ORDER BY AVGY DESC) accLCQ
7 FROM (
8 SELECT a.*,SUM(LCQ) over (PARTITION BY PCODE) totLCQ FROM TEST a
9 ) a
10 )
11 WHERE 84 <= accLCQ
12 AND ROWNUM=1;
PCODE LCQ AVGY
------ ----- ----
prod1 1700 92
To satisfy the original requirement, the above query should be extended to PSTAGE, PGROUP, PLGROUP, PPLAN groupings which in this case is so tricky. My approach is through GROUP BY ROLLUP(PSTAGE, PGROUP, PLGROUP, PPLAN, PCODE, LTYPE, LID) but it gives me lacking output and that is, the 84th percentile column.
SELECT
PSTAGE,
PGROUP,
PLGROUP,
PPLAN,
PCODE,
LTYPE,
LID,
ROUND((1-(SUM(CNSQ)-SUM(CMBQ))
/DECODE((SUM(LCQ)+SUM(CNSQ)-SUM(CMBQ)),0,1,NULL,1,(SUM(LCQ) + SUM(CNSQ)-SUM(CMBQ)))
)* 100,2) AVGY,
SUM(LCQ) LCQ,
'?' PER84TH,
(CASE
WHEN GROUPING(LID)=1 AND GROUPING(LTYPE)=1 AND GROUPING(PCODE)=1 AND GROUPING(PPLAN)=1 AND GROUPING(PLGROUP)=1 AND GROUPING(PGROUP)=1 THEN '06.PSTAGE'
WHEN GROUPING(LID)=1 AND GROUPING(LTYPE)=1 AND GROUPING(PCODE)=1 AND GROUPING(PPLAN)=1 AND GROUPING(PLGROUP)=1 THEN '05.PGROUP'
WHEN GROUPING(LID)=1 AND GROUPING(LTYPE)=1 AND GROUPING(PCODE)=1 AND GROUPING(PPLAN)=1 THEN '04.PLGROUP'
WHEN GROUPING(LID)=1 AND GROUPING(LTYPE)=1 AND GROUPING(PCODE)=1 THEN '03.PPLAN'
WHEN GROUPING(LID)=1 AND GROUPING(LTYPE)=1 THEN '02.PCODE'
WHEN GROUPING(LID)=1 THEN '01.LTYPE'
ELSE NULL
END) GROUPING
FROM TEST1
GROUP BY ROLLUP(PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID)
HAVING NOT(GROUPING(LID)=1 AND GROUPING(LTYPE)=1 AND GROUPING(PCODE)=1 AND GROUPING(PPLAN)=1 AND GROUPING(PLGROUP)=1 AND GROUPING(PGROUP)=1 AND GROUPING(PSTAGE)=1)
ORDER BY plant,PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID
Please ignore the CNSQ and CMBQ columns in the above query since I think they are not relevant here. I just put it there as a reference of how the AVGY field is derived.
October 10, 2012 - 7:36 am UTC
... To satisfy the original requirement, the above query should be extended to PSTAGE, PGROUP, PLGROUP, PPLAN groupings which in this case is so tricky ...
why is it tricky? wouldn't you just need to partition by those attributes??? do the same thing you did for pcode, but just partition by pstage, pgroup, plgroup and pplan???
My solution needs improvement
rebisco, October 10, 2012 - 5:38 am UTC
Hi Tom,
I hope you are feeling so well. After wrestling this so tricky problem, I came up with the following solution. I know there still room for improvement in my solution below. Please suggest. I appreciate your help.
SELECT a.*,
ROUND((CASE
WHEN GRP = '01.LTYPE' THEN
(SELECT avgy
FROM (
SELECT a.*,
SUM(lcq*100/totLCQ) over (PARTITION BY pstage,pgroup,plgroup,pplan,pcode,ltype ORDER BY avgy DESC) accLCQ
FROM
(
SELECT a.*,SUM(lcq) over (PARTITION BY pstage,pgroup,plgroup,pplan,pcode,ltype) totLCQ FROM test1 a
) a
) b
WHERE 84 <= accLCQ AND ROWNUM=1
AND b.pstage = a.pstage AND b.pgroup = a.pgroup AND b.plgroup = a.plgroup AND b.pplan = a.pplan AND b.pcode = a.pcode AND b.ltype = a.ltype)
WHEN GRP = '02.PCODE' THEN
(SELECT avgy
FROM (
SELECT a.*,
SUM(lcq*100/totLCQ) over (PARTITION BY pstage,pgroup,plgroup,pplan,pcode ORDER BY avgy DESC) accLCQ
FROM
(
SELECT a.*,SUM(lcq) over (PARTITION BY pstage,pgroup,plgroup,pplan,pcode) totLCQ FROM test1 a
) a
) b
WHERE 84 <= accLCQ AND ROWNUM=1
AND b.pstage = a.pstage AND b.pgroup = a.pgroup AND b.plgroup = a.plgroup AND b.pplan = a.pplan AND b.pcode = a.pcode)
WHEN GRP = '03.PPLAN' THEN
(SELECT avgy
FROM (
SELECT a.*,
SUM(lcq*100/totLCQ) over (PARTITION BY pstage,pgroup,plgroup,pplan ORDER BY avgy DESC) accLCQ
FROM
(
SELECT a.*,SUM(lcq) over (PARTITION BY pstage,pgroup,plgroup,pplan) totLCQ FROM test1 a
) a
) b
WHERE 84 <= accLCQ AND ROWNUM=1
AND b.pstage = a.pstage AND b.pgroup = a.pgroup AND b.plgroup = a.plgroup AND b.pplan = a.pplan)
WHEN GRP = '04.PLGROUP' THEN
(SELECT avgy
FROM (
SELECT a.*,
SUM(lcq*100/totLCQ) over (PARTITION BY pstage,pgroup,plgroup ORDER BY avgy DESC) accLCQ
FROM
(
SELECT a.*,SUM(lcq) over (PARTITION BY pstage,pgroup,plgroup) totLCQ FROM test1 a
) a
) b
WHERE 84 <= accLCQ AND ROWNUM=1
AND b.pstage = a.pstage AND b.pgroup = a.pgroup AND b.plgroup = a.plgroup)
WHEN GRP = '05.PGROUP' THEN
(SELECT avgy
FROM (
SELECT a.*,
SUM(lcq*100/totLCQ) over (PARTITION BY pstage,pgroup ORDER BY avgy DESC) accLCQ
FROM
(
SELECT a.*,SUM(lcq) over (PARTITION BY pstage,pgroup) totLCQ FROM test1 a
) a
) b
WHERE 84 <= accLCQ AND ROWNUM=1
AND b.pstage = a.pstage AND b.pgroup = a.pgroup)
WHEN GRP = '06.PSTAGE' THEN
(SELECT avgy
FROM (
SELECT a.*,
SUM(lcq*100/totLCQ) over (PARTITION BY pstage ORDER BY avgy DESC) accLCQ
FROM
(
SELECT a.*,SUM(lcq) over (PARTITION BY pstage) totLCQ FROM test1 a
) a
) b
WHERE 84 <= accLCQ AND ROWNUM=1
AND b.pstage = a.pstage)
ELSE NULL
END
),2) P84th
FROM
(
SELECT
pstage,pgroup,plgroup,pplan,pcode,ltype,lid,
SUM(lcq) lcq,
SUM(avgy) avgy,
(CASE
WHEN GROUPING_ID(PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID) = 1 THEN '01.LTYPE'
WHEN GROUPING_ID(PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID) = 3 THEN '02.PCODE'
WHEN GROUPING_ID(PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID) = 7 THEN '03.PPLAN'
WHEN GROUPING_ID(PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID) = 15 THEN '04.PLGROUP'
WHEN GROUPING_ID(PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID) = 31 THEN '05.PGROUP'
WHEN GROUPING_ID(PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID) = 63 THEN '06.PSTAGE'
END) GRP
FROM TEST1 a
GROUP BY ROLLUP (PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID)
HAVING NOT(GROUPING_ID(PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID) = 127)
ORDER BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID
) a;
Not as easy as that
rebisco, October 10, 2012 - 8:43 pm UTC
Hi Tom,
"why is it tricky? wouldn't you just need to partition by those attributes??? do the same thing you did for pcode, but just partition by pstage, pgroup, plgroup and pplan???"
I don't know what you mean by "just partition on those attributes". I want to get the 84th percentile on each groupings (PSTAGE, PGROUP, PLGROUP, PPLAN, PCODE and LTYPE) how can I use the "Partition" clause in this case? Please elaborate more on your suggestion.
Thank you,
rebisco
October 11, 2012 - 7:36 am UTC
you did it with
SQL> SELECT PCODE, LCQ, AVGY
2 FROM
3 (
4 SELECT
5 a.*,
6 SUM(LCQ*100/totLCQ) over (PARTITION BY PCODE ORDER BY AVGY DESC) accLCQ
7 FROM (
8 SELECT a.*,SUM(LCQ) over (PARTITION BY PCODE) totLCQ FROM TEST a
9 ) a
10 )
11 WHERE 84 <= accLCQ
12 AND ROWNUM=1;
so, now, if you need it by
(PSTAGE, PGROUP, PLGROUP, PPLAN, PCODE and LTYPE)
partition by that (use row_number() over ( partition by PSTAGE, PGROUP, PLGROUP, PPLAN, PCODE and LTYPE order by whatever) to get the first row from each group)
More explanation
rebisco, October 11, 2012 - 9:37 am UTC
Hi Tom,
I think, I did not explained it well? Here, I will try to explain it more. I don't want to get the first row for each group only but, get the 84th percentile of each "GROUPINGs" together with its running subtotal. If you can just run the long query I posted before this post, you can see the whole output I wanted. Although, it gives me the correct answer I needed, the query for "P84th" column basically needs more attention. The output basically are grouped by PSTAGE, PGROUP, etc and a running subtotal of each grouping. The 84th percentile is generated based on AVGY after computation is made (using the accumulated LCQ) of each grouping.
By the way, from your suggestion, I don't know how should I modify the query using the "row_number() over ( partition by PSTAGE, PGROUP, PLGROUP, PPLAN, PCODE and LTYPE order by whatever)". Please post the whole modified query here which uses the row_number() over (partition by ...).
Thank you,
rebisco
October 11, 2012 - 9:49 am UTC
If you can just run the long query I posted before this post, you can see the whole output I wanted.they say a picture is worth a thousand words - but in this case - words are necessary (a specification). a picture of a report from a query (that may or may not be semantically correct, just because it happens to paint a certain picture with your small test data doesn't mean it is correct) doesn't tell us what to do.
it would look something like:
SELECT PCODE, LCQ, AVGY
FROM
(
SELECT a.*,
SUM(LCQ*100/totLCQ) over (PARTITION BY PCODE ORDER BY AVGY DESC) accLCQ,
row_number() over (partition by pcode order by avgy desc) rn
FROM ( SELECT a.*,SUM(LCQ) over (PARTITION BY PCODE) totLCQ
FROM TEST a) a
)
WHERE 84 <= accLCQ
AND rn=1;
with row_number(), using a different partitioning scheme.
rebisco, October 11, 2012 - 9:22 pm UTC
Hi Tom,
Thank you. It does gave me the correct output I needed. I just need to UNION ALL for all the needed groupings like PSTAGE, PGROUP, etc. One more thing though, if I need to compute another percentile, say 95th percentile, that is, 95 <= accLCQ, do I need to create a separate UNION ALL block just for this? Isn't it logical to use GROUP BY ROLLUP (PSTAGE, PGROUP, etc)? Please advice.
Thanks again,
rebisco
October 12, 2012 - 7:52 am UTC
how would aggregation help you with a percentile??
(and have you investigated the percentile_cont/disc analytics?
you could use another layer of query to get the first row for 84 <= accLCQ and then 95 <= accLCQ
final query
rebisco, October 12, 2012 - 9:03 am UTC
Hello Tom,
Since I want to compute the running subtotal of each groupings together with the percentile(84th and 95th), I'm thinking maybe ROLLUP GROUP BY(...) could help?
Yes, I tried the percentile_disc but, I'm not sure if this really helps me. Percentile_disc will just compute base on the input sets. That is, will return part of the sets that fall on the given percentile value. This is not in my case. I'm getting the percentile on LCQ but the returned value will be retrieved from AVGY. Also, percentile_disc will not cater on 2-digit decimal value(at least in my testing) ie 0.84 or 0.95? it will be rounded off to 0.8 and 1 when it is used. I'm not sure with the percentile_cont though.
For your suggestion, I think I need to get the 95th percentile first before the 84th? Below is the final query I build which gives me the correct output I needed. Maybe there still improvement for this query. Please advice.
SELECT *
FROM
(
SELECT PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID,LCQ,CNSQ,CMBQ,AVGY,0 P84TH, 0 P95TH, NULL GROUPING FROM TEST2
UNION ALL
SELECT PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,NULL,totLCQ LCQ,totCNSQ,totCMBQ,round(totAVGY*100,2), AVGY P84TH, P95TH, '01.LTYPEE' GROUPING
FROM
(
SELECT a.*, avgy p95th
FROM
(
SELECT a.*,row_number() over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE ORDER BY accLCQ DESC) rn,
(1-(totCNSQ-totCMBQ)/(totLCQ+totCNSQ-totCMBQ)) totAVGY
FROM
(
SELECT a.*,
SUM(LCQ*100/decode(totLCQ,0,1,totLCQ)) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE ORDER BY AVGY DESC) accLCQ
FROM ( SELECT a.*,
SUM(LCQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE) totLCQ,
SUM(CNSQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE) totCNSQ,
SUM(CMBQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE) totCMBQ
FROM TEST2 a) a
) a
) a WHERE 95 <= accLCQ AND rn=1
) a WHERE 84 <= accLCQ AND rn=1
UNION ALL
SELECT PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,NULL,NULL,totLCQ LCQ,totCNSQ,totCMBQ,round(totAVGY*100,2), AVGY P84TH, P95TH, '02.PCODE' GROUPING
FROM
(
SELECT a.*, avgy p95th
FROM
(
SELECT a.*,row_number() over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE ORDER BY accLCQ DESC) rn,
(1-(totCNSQ-totCMBQ)/(totLCQ+totCNSQ-totCMBQ)) totAVGY
FROM
(
SELECT a.*,
SUM(LCQ*100/decode(totLCQ,0,1,totLCQ)) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE ORDER BY AVGY DESC) accLCQ
FROM ( SELECT a.*,
SUM(LCQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE) totLCQ,
SUM(CNSQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE) totCNSQ,
SUM(CMBQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE) totCMBQ
FROM TEST2 a) a
) a
) a WHERE 95 <= accLCQ AND rn=1
) a WHERE 84 <= accLCQ AND rn=1
UNION ALL
SELECT PSTAGE,PGROUP,PLGROUP,PPLAN,NULL,NULL,NULL,totLCQ LCQ,totCNSQ,totCMBQ,round(totAVGY*100,2), AVGY P84TH, P95TH, '03.PPLAN' GROUPING
FROM
(
SELECT a.*, avgy p95th
FROM
(
SELECT a.*,row_number() over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN ORDER BY accLCQ DESC) rn,
(1-(totCNSQ-totCMBQ)/(totLCQ+totCNSQ-totCMBQ)) totAVGY
FROM
(
SELECT a.*,
SUM(LCQ*100/decode(totLCQ,0,1,totLCQ)) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN ORDER BY AVGY DESC) accLCQ
FROM ( SELECT a.*,
SUM(LCQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN) totLCQ,
SUM(CNSQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN) totCNSQ,
SUM(CMBQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP,PPLAN) totCMBQ
FROM TEST2 a) a
) a
) a WHERE 95 <= accLCQ AND rn=1
) a WHERE 84 <= accLCQ AND rn=1
UNION ALL
SELECT PSTAGE,PGROUP,PLGROUP,NULL,NULL,NULL,NULL,totLCQ LCQ,totCNSQ,totCMBQ,round(totAVGY*100,2), AVGY P84TH, P95TH, '04.PLGROUP' GROUPING
FROM
(
SELECT a.*, avgy p95th
FROM
(
SELECT a.*,row_number() over (PARTITION BY PSTAGE,PGROUP,PLGROUP ORDER BY accLCQ DESC) rn,
(1-(totCNSQ-totCMBQ)/(totLCQ+totCNSQ-totCMBQ)) totAVGY
FROM
(
SELECT a.*,
SUM(LCQ*100/decode(totLCQ,0,1,totLCQ)) over (PARTITION BY PSTAGE,PGROUP,PLGROUP ORDER BY AVGY DESC) accLCQ
FROM ( SELECT a.*,
SUM(LCQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP) totLCQ,
SUM(CNSQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP) totCNSQ,
SUM(CMBQ) over (PARTITION BY PSTAGE,PGROUP,PLGROUP) totCMBQ
FROM TEST2 a) a
) a
) a WHERE 95 <= accLCQ AND rn=1
) a WHERE 84 <= accLCQ AND rn=1
UNION ALL
SELECT PSTAGE,PGROUP,NULL,NULL,NULL,NULL,NULL,totLCQ LCQ,totCNSQ,totCMBQ,round(totAVGY*100,2), AVGY P84TH, P95TH, '05.PGROUP' GROUPING
FROM
(
SELECT a.*, avgy p95th
FROM
(
SELECT a.*,row_number() over (PARTITION BY PSTAGE,PGROUP ORDER BY accLCQ DESC) rn,
(1-(totCNSQ-totCMBQ)/(totLCQ+totCNSQ-totCMBQ)) totAVGY
FROM
(
SELECT a.*,
SUM(LCQ*100/decode(totLCQ,0,1,totLCQ)) over (PARTITION BY PSTAGE,PGROUP ORDER BY AVGY DESC) accLCQ
FROM ( SELECT a.*,
SUM(LCQ) over (PARTITION BY PSTAGE,PGROUP) totLCQ,
SUM(CNSQ) over (PARTITION BY PSTAGE,PGROUP) totCNSQ,
SUM(CMBQ) over (PARTITION BY PSTAGE,PGROUP) totCMBQ
FROM TEST2 a) a
) a
) a WHERE 95 <= accLCQ AND rn=1
) a WHERE 84 <= accLCQ AND rn=1
UNION ALL
SELECT PSTAGE,NULL,NULL,NULL,NULL,NULL,NULL,totLCQ LCQ,totCNSQ,totCMBQ,round(totAVGY*100,2), AVGY P84TH, P95TH, '06.PSTAGE' GROUPING
FROM
(
SELECT a.*, avgy p95th
FROM
(
SELECT a.*,row_number() over (PARTITION BY PSTAGE ORDER BY accLCQ DESC) rn, (1-(totCNSQ-totCMBQ)/(totLCQ+totCNSQ-totCMBQ)) totAVGY
FROM
(
SELECT a.*,
SUM(LCQ*100/decode(totLCQ,0,1,totLCQ)) over (PARTITION BY PSTAGE ORDER BY AVGY DESC) accLCQ
FROM ( SELECT a.*,
SUM(LCQ) over (PARTITION BY PSTAGE) totLCQ,
SUM(CNSQ) over (PARTITION BY PSTAGE) totCNSQ,
SUM(CMBQ) over (PARTITION BY PSTAGE) totCMBQ
FROM TEST2 a) a
) a
) a WHERE 95 <= accLCQ AND rn=1
) a WHERE 84 <= accLCQ AND rn=1
)
ORDER BY PSTAGE,PGROUP,PLGROUP,PPLAN,PCODE,LTYPE,LID;Thanks again,
rebisco
Some clarification
rebisco, October 19, 2012 - 4:59 am UTC
Hello Tom,
I just want to align my solution on your comment above.
you could use another layer of query to get the first row for 84 <= accLCQ and then 95 <= accLCQ
How could I do this?
Thanks again,
rebisco
Can I do this using Group by rollup?
rebisco, September 30, 2013 - 1:28 pm UTC
Hi Tom,
I have a data like below:
PG PL PR STEP STDCAT RC LID QTY
--- --- --- ----- ------ --- --- ----------
pg1 pl1 pr1 step1 std1 rc1 id1 5
pg1 pl1 pr1 step1 std2 rc1 id1 10
pg1 pl1 pr1 step1 std2 rc2 id2 5
pg1 pl1 pr1 step2 std1 rc3 id3 15
I want to have an output like below:
PL PR STEP STDCAT RC LID NUMLOT QTY GRP
--- --- ----- ------ --- --- ---------- ---------- -----------
pl1 pr1 step1 std1 rc1 id1 0 5
pl1 pr1 step1 std1 rc1 1 5 RCGROUP
pl1 pr1 step1 std1 1 5 STDCATGROUP
pl1 pr1 step1 std2 rc1 id1 0 5
pl1 pr1 step1 std2 rc1 1 5 RCGROUP
pl1 pr1 step1 std2 rc2 id2 0 10
pl1 pr1 step1 std2 rc2 1 10 RCGROUP
pl1 pr1 step1 std2 2 15 STDCATGROUP
pl1 pr1 step1 2 15 STEPGROUP
pl1 pr1 step2 std1 rc3 id3 0 15
pl1 pr1 step2 std1 rc3 1 15 RCGROUP
pl1 pr1 step2 std1 1 15 STDCATGROUP
pl1 pr1 step2 1 15 STEPGROUP
pl1 pr1 3 30 PRGROUP
pl1 3 30 PLGROUP
Can I use Group By Rollup in this case?
Thanks a lot.
Ask in Interview
Aryan, June 01, 2017 - 6:46 am UTC
How to perform " rollup " operation on select statement without using " group by rollup?
Is it possibe?
June 01, 2017 - 11:01 am UTC
Why do people ask "how do you do task X without using feature Y specifically design for X?"!? They're just a cheap shot to try and trip people up.
Anyway, there's plenty of ways to do a rollup without using it. Here's a few:
- Union together the separate group bys
- Group by cube and filter out the groups you wouldn't get with rollup
- Use grouping sets to define the same groups