Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, chirayu.

Asked: May 07, 2003 - 9:48 am UTC

Last updated: October 21, 2008 - 11:40 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Dear Tom,
I have a query like this,

SELECT vplcode, SUM(rated_flat_amount)/COUNT(*) amt
FROM tap_rtx_009
WHERE vplcode = 223
GROUP BY vplcode, start_d_t, actual_volume,
rated_flat_amount
HAVING COUNT(*) > 1

---- result ----


vplcode amt
------- ---
223 2
223 3
223 9
223 1
223 5


what I want is a grand total at the end ie.


vplcode amt
------- ---
223 2
223 3
223 9
223 1
223 5
20

Is it possible, can analytic functions be of use in this..

thanks in advance


and Tom said...

group by rollup does this:

scott@ORA920> select grouping(deptno), grouping(job),
2 deptno, sum(sal)/count(*) amt
3 from emp
4 group by ROLLUP(deptno, job)
5 /

GROUPING(DEPTNO) GROUPING(JOB) DEPTNO AMT
---------------- ------------- ---------- ----------
0 0 10 1300
0 0 10 2450
0 0 10 5000
0 1 10 2916.66667
0 0 20 950
0 0 20 3000
0 0 20 2975
0 1 20 2175
0 0 30 950
0 0 30 2850
0 0 30 1400
0 1 30 1566.66667
1 1 2073.21429

13 rows selected.

if you are not interested in the intermediate subtotals, a having clause will filter them out:

scott@ORA920> select grouping(deptno), grouping(job),
2 deptno, sum(sal)/count(*) amt
3 from emp
4 group by ROLLUP(deptno, job)
5 having grouping(deptno) = 1 OR
6 (grouping(deptno)+grouping(job) = 0)
7 /

GROUPING(DEPTNO) GROUPING(JOB) DEPTNO AMT
---------------- ------------- ---------- ----------
0 0 10 1300
0 0 10 2450
0 0 10 5000
0 0 20 950
0 0 20 3000
0 0 20 2975
0 0 30 950
0 0 30 2850
0 0 30 1400
1 1 2073.21429

10 rows selected.



Rating

  (32 ratings)

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

Comments

A reader, May 07, 2003 - 10:30 am UTC


but want something more

chirayu sutaria, May 08, 2003 - 1:08 am UTC

Dear Tom,
Thanks for the prompt reply..
but I think I was not able to articulate my question properly..
moreover I am also learning the analytics, rollup, cube,
so not well equipped to apply these advanced features appropriately.
Please help me out.

If you would look at my query again..

SELECT vplcode, SUM(rated_flat_amount)/COUNT(*) amt
FROM tap_rtx_009
WHERE vplcode = 223
GROUP BY vplcode, start_d_t, actual_volume,
rated_flat_amount
HAVING COUNT(*) > 1

I have four fields in my group by clause,
so when I modified my query ..



SELECT grouping(vplcode), SUM(rated_flat_amount)/COUNT(*) amt
FROM tap_rtx_009
WHERE vplcode = 223
GROUP BY rollup(vplcode, start_d_t, actual_volume,
rated_flat_amount)
HAVING COUNT(*) > 1 AND grouping(vplcode) = 1

I got the result set as
GROUPING(VPLCODE) AMT
1 7.48142587770495

I think I had made some mistake somewhere..

Please also see that I have four columns in the group by clause
and I am filtering the query to return only those rows who have a count greater
than one..

Hope this clarifies my doubt...



Tom Kyte
May 08, 2003 - 9:38 am UTC

add the other columns in (i've never understood why they wouldn't be there)

select out their grouping() values as well

use that output to clearly identify the rows you want.


use that in the having clause. Believe it or not, that is what I do -- i always forget if 0 is aggregates and 1's are details or vice versa -- so I run it, isolate the rows I want -- add the predicate and away you go

What difference does it make when.....

pasko, May 08, 2003 - 2:56 am UTC

Hi Tom ,
Thanks for a great trick above for filtering unwanted rows :
"having grouping(deptno) = 1 OR
(grouping(deptno)+grouping(job) = 0)"

I have tried this Rollup stuff myself and i have a question related slightly to this :

1.
Is the order of columns in the rollup statement related
in any way to the group by columns from the select list ?

2.I note that i get different results when i reverse the order of columns inside the rollup Brackets ..is this expected behavior ?

For Example :
These queries don't seem to return same Results :

select grouping(deptno), grouping(job),
deptno, job ,
sum(sal) amt
from emp
group by ROLLUP(deptno, job)


select grouping(deptno), grouping(job),
deptno, job ,
sum(sal) amt
from emp
group by ROLLUP( job , deptno ) -- reversed order of columns


select grouping(deptno), grouping(job),
job , deptno , -- reversed order of columns

sum(sal) amt
from emp
group by ROLLUP( job , deptno )

sorry i don't have scott sample schema setup right here with me. :-(

Thanks in advance.


Tom Kyte
May 08, 2003 - 9:46 am UTC

1) 100%

2) yes. The first rollup(deptno,job) will sort (rollup sorts) by deptno, job -- giving you subtotals by job within deptno and by deptno and totals for the entire set.

rollup(job,deptno) will sort by job, deptno -- giving you subtotals by deptno within job and then by job and then totals


scott@ORA817DEV> select deptno, job, sum(sal),
2 decode( grouping(job),1,'<===job'),
3 decode( grouping(deptno),1,'<===deptno')
4 from emp group by rollup(deptno,job)
5 /

DEPTNO JOB SUM(SAL) DECODE( DECODE(GRO
---------- --------- ---------- ------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <===job
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875 <===job
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <===job
29025 <===job <===deptno

13 rows selected.

scott@ORA817DEV> select deptno, job, sum(sal),
2 decode( grouping(job),1,'<===job'),
3 decode( grouping(deptno),1,'<===deptno')
4 from emp group by rollup(job,deptno)
5 /

DEPTNO JOB SUM(SAL) DECODE( DECODE(GRO
---------- --------- ---------- ------- ----------
20 ANALYST 6000
ANALYST 6000 <===deptno
10 CLERK 1300
20 CLERK 1900
30 CLERK 950
CLERK 4150 <===deptno
10 MANAGER 2450
20 MANAGER 2975
30 MANAGER 2850
MANAGER 8275 <===deptno
10 PRESIDENT 5000
PRESIDENT 5000 <===deptno
30 SALESMAN 5600
SALESMAN 5600 <===deptno
29025 <===job <===deptno

15 rows selected.



everybody that has sqlplus has emp and dept. $ORACLE_HOME/sqlplus/demo/demobld.sql. you can put it into your own schema, doesn't have to be SCOTT


Thanks very much...mostly appreciated ....

A reader, May 09, 2003 - 3:19 am UTC


anything new in 9i

john, April 05, 2004 - 11:14 pm UTC

Tom,

in the example quoted below,

select deptno, job, sum(sal),
2 decode( grouping(job),1,'<===job'),
3 decode( grouping(deptno),1,'<===deptno')
4 from emp group by rollup(deptno,job)

say, if i don't want sub totals for job, i need totals only based on deptno. can't we do this without using having clause in 9i?




Tom Kyte
April 06, 2004 - 8:59 am UTC

1 select deptno, job, sum(sal), decode(grouping(job),1,'<<<==' ) grp
2 from emp
3* group by grouping sets((deptno,job),(deptno))
scott@ORA10G> /

DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <<<==
20 CLERK 1900
20 ANALYST 10781.24
20 MANAGER 2975
20 15656.24 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==

12 rows selected.



no subtotal for single row

John, April 22, 2004 - 1:22 am UTC

Tom,

Can we avoid displaying the subtotal for single rows without using having clause?

like for example, if you have deptno 40 job CLERK, the below query gives me the output:

1 select deptno, job, sum(sal), decode(grouping(job),1,'<<<==' ) grp
2 from emp
3* group by grouping sets((deptno,job),(deptno))
scott@ORA10G> /

DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <<<==
20 CLERK 1900
20 ANALYST 10781.24
20 MANAGER 2975
20 15656.24 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==
40 CLERK 3600
40 3600 <<<==

12 rows selected.

Tom Kyte
April 22, 2004 - 7:31 am UTC

ops$tkyte@ORA9IR2> select deptno, job, sum(sal), decode(grouping(job),1,'<<<==' ) grp
  2    from emp
  3   group by grouping sets((deptno,job),(deptno))<b>
  4   having count(*) > 1 OR grouping(job) <> 1</b>
  5  /
 
    DEPTNO JOB         SUM(SAL) GRP
---------- --------- ---------- -----
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750 <<<==
        20 CLERK           1900
        20 ANALYST         6000
        20 MANAGER         2975
        20                10875 <<<==
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400 <<<==
        40 CLERK
 
 

A reader, August 27, 2004 - 11:29 pm UTC


A reader, August 19, 2005 - 2:13 pm UTC


Grand total

VA, December 20, 2005 - 11:04 pm UTC

But can this feature be used to stick a total on any column in an existing report?

Say I have a report on
select * from emp

I just want to add a row with the grand totals for the SAL and COMM columns. How can I do this?

Thanks

Tom Kyte
December 21, 2005 - 7:17 am UTC

group by rollup or group by grouping sets can do that if needed.

scott@ORA10GR2> select empno, ename, sum(sal), sum(comm) from emp group by grouping sets ((empno,ename),());

EMPNO ENAME SUM(SAL) SUM(COMM)
---------- ---------- ---------- ----------
7900 JAMES 950
7369 SMITH 800
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975
7654 MARTIN 1250 1400
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500 0
7876 ADAMS 1100
7902 FORD 3000
7934 MILLER 1300
29025 2200

15 rows selected.


Or ..

VA, December 21, 2005 - 10:26 am UTC

I came up with the following

select
decode(grouping_id(empno),1,'Grand Total',empno) empno,
ename,job,deptno,
sum(sal) sum_sal,
avg(comm) avg_comm,
max(sal) max_sal
from emp
group by rollup(empno,ename,job,deptno)
having grouping_id(empno,ename,job,deptno) in (0,15)

The GROUPING_ID gives a binary "bitmap" of which columns are showing a detail row vs. a aggregate/super-aggregate row. In this case, we want to show the lowest level of detail (0) and the highest grand total (15=binary 1111 or all bits turned on).

Yes, it is a little wasteful to generate intermediate aggregates and throw them away, but it works and it is fast!

Thanks

Tom Kyte
December 21, 2005 - 7:36 pm UTC

grouping sets only does the ones you need and () is pretty easy to type ;)

grouping sets

VA, December 21, 2005 - 8:38 pm UTC

Not sure I understand. How would my query above be re-written using grouping sets and produce identical results?

Thanks

A reader, December 21, 2005 - 8:44 pm UTC

I got it, sorry for not trying this before asking you.

select
decode(grouping_id(empno),1,'Grand Total',empno) empno,
ename,job,deptno,
sum(sal) sum_sal,
avg(comm) avg_comm,
max(sal) max_sal
from emp
group by grouping sets ( (empno,ename,job,deptno), () )

would produce the same result as

select
decode(grouping_id(empno),1,'Grand Total',empno) empno,
ename,job,deptno,
sum(sal) sum_sal,
avg(comm) avg_comm,
max(sal) max_sal
from emp
group by rollup(empno,ename,job,deptno)
having grouping_id(empno,ename,job,deptno) in (0,15)

and probably be a little more efficient.

Order of rows after rollup

A reader, May 06, 2006 - 6:10 am UTC

How can I get the following output, specially the
order it is in using the sample TIME DIMENSION.

Year Qtr Month Sales
1991 110,000
1991 Q1 5,000
1991 Q1 JAN 1,000
1991 Q1 FEB 1,000
.
.
.
1991 Q2 50,000
1991 Q2 JAN 20,000
.
.
.
1992 200000
1992 Q1 1000
.
.

ROLLUP puts the made up aggregated to row
at last, but I want it on top.

Thanks


Tom Kyte
May 07, 2006 - 11:22 am UTC

well, rollup lets you use grouping to see what dimensions have been aggregated over, so you can first query:

ops$tkyte@ORA10GR2> select deptno, job, ename, sum(sal) sum_sal,
  2         grouping(deptno) gd, grouping(job) gj, grouping(ename) ge
  3    from emp
  4   group by rollup( deptno, job, ename );

    DEPTNO JOB       ENAME         SUM_SAL         GD         GJ         GE
---------- --------- ---------- ---------- ---------- ---------- ----------
        10 CLERK     MILLER           1300          0          0          0
        10 CLERK                      1300          0          0          1
        10 MANAGER   CLARK            2450          0          0          0
        10 MANAGER                    2450          0          0          1
        10 PRESIDENT KING             5000          0          0          0
        10 PRESIDENT                  5000          0          0          1
        10                            8750          0          1          1
        20 CLERK     ADAMS            1100          0          0          0
        20 CLERK     SMITH             800          0          0          0
        20 CLERK                      1900          0          0          1
        20 ANALYST   FORD             3000          0          0          0
        20 ANALYST   SCOTT            3000          0          0          0
        20 ANALYST                    6000          0          0          1
        20 MANAGER   JONES            2975          0          0          0
        20 MANAGER                    2975          0          0          1
        20                           10875          0          1          1
        30 CLERK     JAMES             950          0          0          0
        30 CLERK                       950          0          0          1
        30 MANAGER   BLAKE            2850          0          0          0
        30 MANAGER                    2850          0          0          1
        30 SALESMAN  WARD             1250          0          0          0
        30 SALESMAN  ALLEN            1600          0          0          0
        30 SALESMAN  MARTIN           1250          0          0          0
        30 SALESMAN  TURNER           1500          0          0          0
        30 SALESMAN                   5600          0          0          1
        30                            9400          0          1          1
                                     29025          1          1          1

27 rows selected.

<b>to get a feel for what it looks like - then, you can order by those columns - putting the 1's first in each group:</b>

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select deptno, job, ename, sum_sal, gd, gj, ge
  2    from (
  3  select deptno, job, ename, sum(sal) sum_sal,
  4         grouping(deptno) gd, grouping(job) gj, grouping(ename) ge
  5    from emp
  6   group by rollup( deptno, job, ename )
  7         )
  8   order by gd DESC, deptno, gj DESC, job, ge DESC, ename
  9  /

    DEPTNO JOB       ENAME         SUM_SAL         GD         GJ         GE
---------- --------- ---------- ---------- ---------- ---------- ----------
                                     29025          1          1          1
        10                            8750          0          1          1
        10 CLERK                      1300          0          0          1
        10 CLERK     MILLER           1300          0          0          0
        10 MANAGER                    2450          0          0          1
        10 MANAGER   CLARK            2450          0          0          0
        10 PRESIDENT                  5000          0          0          1
        10 PRESIDENT KING             5000          0          0          0
        20                           10875          0          1          1
        20 ANALYST                    6000          0          0          1
        20 ANALYST   FORD             3000          0          0          0
        20 ANALYST   SCOTT            3000          0          0          0
        20 CLERK                      1900          0          0          1
        20 CLERK     ADAMS            1100          0          0          0
        20 CLERK     SMITH             800          0          0          0
        20 MANAGER                    2975          0          0          1
        20 MANAGER   JONES            2975          0          0          0
        30                            9400          0          1          1
        30 CLERK                       950          0          0          1
        30 CLERK     JAMES             950          0          0          0
        30 MANAGER                    2850          0          0          1
        30 MANAGER   BLAKE            2850          0          0          0
        30 SALESMAN                   5600          0          0          1
        30 SALESMAN  ALLEN            1600          0          0          0
        30 SALESMAN  MARTIN           1250          0          0          0
        30 SALESMAN  TURNER           1500          0          0          0
        30 SALESMAN  WARD             1250          0          0          0

27 rows selected.


<b>Note: this is - the rollup is - one of the few times you can get ordered data without using an ORDER BY (cube being the other) in a realiable fashion, every other time, you have to use order by to get data ordered.  group by and cube return data in a predicable "order", we can use order by to rearrange that as fits our needs</b> 

Is inline view necessary?

A reader, May 08, 2006 - 1:57 am UTC

Thank you, thats a good solution.

1) Can we generalize this technique for concatenated and
composite group by
(e.g GROUP BY col1, ROLLUP(col2, (col3,col4))?

2) Is it necessary to use inline view? What will
be the difference between your query and this (output
wise and/or performance wise).

select deptno, job, ename, sum(sal) sum_sal,
grouping(deptno) gd, grouping(job) gj, grouping
(ename) ge
from emp
group by rollup( deptno, job, ename )
order by grouping(deptno) DESC, deptno, grouping(job) DESC,
job, grouping(ename) DESC, ename.

3) Page 18-22 SQL Referrence 9.2
"
The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause."

So I will assume this means if we only use GROUP BY alone,
the rows will be ORDERED if ROLLUP, CUBE or GROUPING SETS
or concatenated (e.g GROUP BY col1, ROLLUP(col2))is used, right? OR is it a documentation bug?.





Tom Kyte
May 08, 2006 - 8:12 am UTC

1) play with grouping_id/grouping functions - you can make it do whatever you want.

2) I like inline views for readability and incremental development. You can order by the functions - sure.

should be "the same" as view merging kicks in and Oracle sees them as the same query

3) this is group by ROLLUP, CUBE and GROUPING sets.

You are not just using a group by here - you are using a group by rollup. The group by extensions return the results in an order by the grouping functions (details then aggregates then aggregated aggregates...)

replacing group by with group by grouping sets

A reader, May 09, 2006 - 2:11 am UTC

Ok, so can I say that both of the following queries
are exactly equivialent.

1- SELECT ... FROM t1 GROUP BY col1,col2 ORDER BY col1,col2;
2- SELECT ... FROM t1 GROUP BY GROUPING SETS((col1,col2));

if yes, then this means every query that has
a GROUP BY only clause with ORDER BY can be converted
into GROUP BY GROUPING SETS without ORDER BY query.
right?


Tom Kyte
May 09, 2006 - 7:53 am UTC

I believe you can only say "the grouping sets will be returned - each in order"



so "no", i would not say that is "right"


In fact:

ops$tkyte@ORA10GR2> select ename, deptno, sum(sal),
  2         grouping(ename) ge, grouping(deptno) gd
  3    from emp
  4   group by grouping sets((ename,deptno))
  5  /
 
ENAME          DEPTNO   SUM(SAL)         GE         GD
---------- ---------- ---------- ---------- ----------
SMITH              20        800          0          0
JONES              20       2975          0          0
FORD               20       3000          0          0
CLARK              10       2450          0          0
SCOTT              20       3000          0          0
TURNER             30       1500          0          0
JAMES              30        950          0          0
KING               10       5000          0          0
ADAMS              20       1100          0          0
BLAKE              30       2850          0          0
MILLER             10       1300          0          0
ALLEN              30       1600          0          0
WARD               30       1250          0          0
MARTIN             30       1250          0          0
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   462 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |    14 |   462 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   462 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 


The groups are returned "predicably". 

meaning of "predicable"

A reader, May 09, 2006 - 8:15 am UTC

What I understand from this is,
if I execute the query

SELECT job, ename, SUM(sal)
FROM emp
GROUP BY GROUPING SETS((job,ename));

the output could be

Job ename SAL
---- ----- ----
Z D 2
A E 3
Z A 4

which is not equivalent to output of

SELECT job, ename, SUM(sal)
FROM emp
GROUP BY job, ename ORDER BY job, ename.

right?

So this means that even when using ROLLUP, CUBE,or
GROUPING SETS, the ORDER of rows are not
completely guaranteed until we use ORDER BY,
we can only predict that the aggregated rows will appear in last but not guarantee
the ORDER of data, like job=Z can appear before job=A.
right?



Tom Kyte
May 09, 2006 - 8:30 am UTC

correct. as demonstrated right above.

group by grouping sets and average calculation

A reader, September 09, 2006 - 9:13 am UTC

I tried the group by grouping sets. In my scenario i wanted the grand totals of aggregate functions like count, sum, avg
. I get correct grand total values for aggregate functions like count, sum (ie grand total of count & sum values ) but in case of average the group by grouping sets does not give the value which is required (which is average of all averages).
To illustrate
Location AVG_OPEN
------------ -------------------
US 13
NON US 26
16

Its calculating the individual averages correctly like US - 13 and NON US - 26
but the running total for the average should have been (13+26)/2 = 19.5
but its giving me 16 which is nowhere nearby.

i am using the query something like this (not the exact query)
select id,name, sum(decode(ACTIVITY.STATUS,'Open',1,0)) ,
round(avg( decode(ACTIVITY.STATUS,'Open',ACTIVITY.STATUS_DIFF)),1)
from <table names> where <clauses>
group by grouping sets (id,name,())

In the above the sum aggregate gives me a correct grand total but the avg grand total gives a value which is not the average of averages calculated


Tom Kyte
September 09, 2006 - 12:23 pm UTC

well, it did the right "thing"

maybe you want to use the analytic function average - which can compute a running total sort of average.

but I don't really have an example to work with...

group by grouping sets & analytical functions

A reader, September 11, 2006 - 9:58 am UTC

Hi Tom

Yeah I got it. Thanks for the clarification.
So its not necessary that the average of all individual averages is the same as the grand total average as you mentioned & Oracle was giving the right average.

To give a view of the data I have which i used to calculate:

Days Count Avg
==================
50 5 10
10 1 10
5 1 5
2 2 1

So the group by grouping sets will give me a grand total average which will be something like this (67/9) = 7.44

where as what we need to show in the report is the running total for the average column (10+10+5+1) = 26/4 = 6.5.

Here is the query I have used :

*******************************************************
select DEALER.DEALER_CDE, DEALER.COMPANY_NAME,
sum(decode(ACTIVITY.STATUS,'New',1,0)) ,
sum(decode(ACTIVITY.STATUS,'Open',1,0)) ,
round(avg( decode (ACTIVITY.STATUS,'Open',ACTIVITY.STATUS_DIFF)),1) ,
sum(decode(ACTIVITY.STATUS,'Sent',1,0)) ,
round(avg( decode (ACTIVITY.status,'Sent',ACTIVITY.STATUS_DIFF)),1) ,
sum(decode(ACTIVITY.STATUS,'Dead',1,0)) ,
round(avg( decode (ACTIVITY.STATUS,'Dead',ACTIVITY.STATUS_DIFF)),1)
from ACTIVITY, REQUEST , DEALER
where REQUEST.DEALER_NUMBER = DEALER.DEALER_ID
and REQUEST.CONFIRMATION='Y' and REQUEST.REQUEST_ID = ACTIVITY.REQUEST_ID
and REQUEST.REQUEST_TYPE='T' and ACTIVITY.ACTIVITY_DATE
between to_date('2006-09-01','YYYY-MM-DD') and to_date('2006-09-09' ,'YYYY-MM-DD')
group by grouping sets((DEALER.DEALER_CDE, DEALER.COMPANY_NAME),
()) order by DEALER.COMPANY_NAME


*******************************************************

So if you can give me a hint how to go about applying the anlaytical function average in the above scenario that will be very helpful

Regards,






Tom Kyte
September 11, 2006 - 10:40 am UTC

need create table, insert into's to play with.

But presuming you have a query that returns that data set (sorted), you would:

ops$tkyte%ORA10GR2> select days, cnt, avrg,
  2         sum(avrg) over (order by r) running_tot,
  3             count(*)  over (order by r) running_cnt,
  4         sum(avrg) over (order by r) /
  5             count(*)  over (order by r) your_avg
  6    from (
  7  select rownum r, t.*
  8    from (select * from t) t
  9         )
 10  /

      DAYS        CNT       AVRG RUNNING_TOT RUNNING_CNT   YOUR_AVG
---------- ---------- ---------- ----------- ----------- ----------
        50          5         10          10           1         10
        10          1         10          20           2         10
         5          1          5          25           3 8.33333333
         2          2          1          26           4        6.5


Your query is my select * from t on line 8 - it would have the ordering/whatever in it. 

A similar Question

Mette, November 21, 2006 - 6:23 pm UTC

Hi Tom.

I have a query and I want to have the "grand total" last.

select count(*) Antal,
case when grouping_id(genrenavn) = 1 then '=== Totalt ===' when b.genrenavn is null then 'Unknown/Null' else b.genrenavn end genrenavn
from enhed a, genre b
where a.genre = b.genre (+)
group by cube(b.genrenavn)
order by 2

The result is

1617 === Totalt ===
4 Alternative
1 AlternRock
1 Ambient
etc
67 Unknown/Null

My preferred result would be:

4 Alternative
1 AlternRock
1 Ambient
etc
67 Unknown/Null
1617 Grand Total (or any other suitable text)




I can force it on top with some special chars (here ===), but how do I get it last (without adding too many strange chars)

regards
Mette


Tom Kyte
November 22, 2006 - 4:39 pm UTC

1 select grouping( deptno), deptno, count(*)
2 from emp
3* group by grouping sets( (deptno), () )
scott%ORA10GR2> /

GROUPING(DEPTNO) DEPTNO COUNT(*)
---------------- ---------- ----------
0 10 3
0 20 5
0 30 6
1 14



is this possible

A reader, November 23, 2006 - 1:34 am UTC

Tom your answers are really excellant...anyways..

my question is that without using

compute sum of sal,comm on deptno,job

i want a query to sum up sal and comm grouped by job and deptno and show all the columns...

below is my query

select EMPNO,ENAME,JOB,MGR,DEPTNO,sum(COMM) comm,sum(SAL) sal from emp group by rollup(DEPTNO,job)



Tom Kyte
November 24, 2006 - 6:15 pm UTC

so, did it work?

Thank you very much :-)

Mette, November 23, 2006 - 6:59 am UTC

Hi Tom,

I really appreciated you answer. I have tried to look into the Oracle SQL Reference for further info regarding grouping sets etc. Is there a better place?

And just a question:

When you dont have the order in the SQL statement in your answer to me, can I always be sure the data is sorted? (I know one normally ALWAYS have to order by to ensure a sorted output)?

Apart from the sort order what is the diff of

select grouping( deptno), deptno, count(*)
from emp
group by grouping sets( (deptno), () )

and

select grouping( deptno), deptno, count(*)
from emp
group by cube ((deptno))
?

regards
Mette

PS Pls. Write a book on rock'n'roll (aka Analytics)

Tom Kyte
November 24, 2006 - 6:22 pm UTC

The data warehousing guide believe it or not should be read by EVERYONE.


they talk about this stuff in greater detail (examples)



A special group by

A reader, November 23, 2006 - 10:15 am UTC

Dear Tom,

I would like to know if you can help me to solve the following situation.

create table t4 (x number, dat DATE, usr VARCHAR2(10))

insert into t4 values (110, sysdate, 'You');

insert into t4 values (110, sysdate, 'Me');

insert into t4 values (110, sysdate, 'Me');

insert into t4 values (110, sysdate, 'You');

select x, dat, usr from t4
group by x,dat, usr

X DAT USR
--------- ------------------- ----------
110 23/11/2006 15:02:24 You
110 23/11/2006 15:02:26 Me
110 23/11/2006 15:02:27 Me
110 23/11/2006 15:02:32 You


I would like to consider that 23/11/2006 15:02:26 = 23/11/2006 15:02:27 and would like to get the following results for the same select

select x, dat, usr from t4
group by x,dat, usr


X DAT USR
--------- ------------------- ----------
110 23/11/2006 15:02:24 You
110 23/11/2006 15:02:26 Me
110 23/11/2006 15:02:32 You

In fact I would like to ignore the 1 sec difference.

Is this possible

Thanks very much for your answer

Best Regards

Tom Kyte
November 24, 2006 - 6:23 pm UTC

umm, that rule you just suggested is very ambigous, care to "explain" it???

what makes two things IN GENERAL equal?

Eliminate Redundant Column Values

A reader, March 08, 2007 - 4:01 pm UTC

Is there a way to format results like the following without knowing how many deptno's?

DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
MANAGER 2450
PRESIDENT 5000
8750 <<<==
20 CLERK 1900
ANALYST 10781.24
MANAGER 2975
15656.24 <<<==
30 CLERK 950
MANAGER 2850
SALESMAN 5600
9400 <<<==

Tom Kyte
March 08, 2007 - 8:37 pm UTC

scott%ORA9IR2> select deptno, job, sum(sal) from emp group by grouping sets( (deptno,job), (deptno) );

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750
        20 CLERK           1900
        20 ANALYST         6000
        20 MANAGER         2975
        20                10875
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400

12 rows selected.



Conditional grouping

Jay, March 09, 2007 - 9:58 am UTC

Good Morning Tom!

Say we have a query like this¿

select deptno, job, sum(sal), decode(grouping(job),1,'<<<==' ) grp
from emp
group by grouping sets((deptno,job),(deptno))

scott@ORA10G> /

DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <<<==
20 CLERK 1900
20 ANALYST 10781.24
20 MANAGER 2975
20 15656.24 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==

I would like to see the details ONLY when a certain criteria is met.

For example, I would to see the results by detail only if the total salary by department is greater than $ 9000, else, I would want to see only the total.

The result set should be something like this:

DEPTNO JOB SUM(SAL) GRP
---------- --------- ---------- -----
10 8750 <<<==
20 CLERK 1900
20 ANALYST 10781.24
20 MANAGER 2975
20 15656.24 <<<==
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <<<==

Would this be something possible to do Tom? Thanks a lot for your time.

b) Tom, can you suggest me some books that I can buy and read to master this language please? I want something that will take me all the way from learning the basics to mastering the language?

Thanks a lot and have a wonderful day!

Tom Kyte
March 09, 2007 - 11:20 am UTC

a)


scott%ORA9IR2> select deptno, job, sum(sal), sumsal,
  2         decode( grouping(job), 1, '<==' ) grp
  3    from (
  4  select deptno, job, sal, sum(sal) over (partition by deptno) sumsal
  5    from emp
  6         )
  7  group by grouping sets((deptno,job,sumsal),(deptno))
  8  having sumsal > 9000 or grouping(job) = 1
  9  /

    DEPTNO JOB         SUM(SAL)     SUMSAL GRP
---------- --------- ---------- ---------- ---
        10                 8750            <==
        20 CLERK           1900      10875
        20 ANALYST         6000      10875
        20 MANAGER         2975      10875
        20                10875            <==
        30 CLERK            950       9400
        30 MANAGER         2850       9400
        30 SALESMAN        5600       9400
        30                 9400            <==

9 rows selected.


b)
sql reference manual
data warehousing guide
EXPERIENCE, PRACTICE



Thanks!

Jay, March 09, 2007 - 11:34 am UTC

Thank you Sir!

Could you please tell me when it would be a good time for me to read your 'expert 1 on 1' book for 9i and up? I am going to buy this one but I just wanted to know if the book is for only the people who have already very good knowledge of pl/sql or for anyone?

Thanks again for your time. I really appreciate it a lot. Thanks!
Tom Kyte
March 12, 2007 - 2:49 pm UTC

it is for everyone - newbies, experienced. It presumes no prior knowledge really of PLSQL and will fill in lots of gaps people with many years of Oracle experience have as well as get someone just starting out well versed in many of the concepts.

Thank you!

Jay, March 12, 2007 - 4:32 pm UTC

Thank you very much Tom!

Rollup with ordered detail rows

tkyte_fan, October 15, 2008 - 6:11 pm UTC

Hi Tom, I have been looking for a way to sort the detail rows within a rollup query.

As you mentioned that group by extensions gives ordered results, but only the aggregates are ordered. How can I get the sorting for leaf data (i.e. detail rows) without impacting the aggregate ordering?

To put in other words, I was looking for something like:
select deptno, job, sum(sal),
       decode( grouping(job),1,'<===job'),
       decode( grouping(deptno),1,'<===deptno')
  from emp group by rollup(job,deptno)
/

    DEPTNO JOB         SUM(SAL) DECODE( DECODE(GRO
---------- --------- ---------- ------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        10                 8750 <===job
        20 ANALYST         6000
        20 CLERK           1900
        20 MANAGER         2975
        20                10875 <===job
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        5600
        30                 9400 <===job
                          29025 <===job <===deptno


If there is a way to ensure that "JOB" column is always in ascending order, and similarly for more than one columns, but also that rollup aggregates appear after/before each group.

Appreciate your help in finding some answer to this. I have searched for this in so many places, including the Data Warehousing guide, but no luck yet.

Thanks.
Tom Kyte
October 17, 2008 - 8:28 pm UTC

you have your rollup backwards there I believe....


you are rolling up over job and then over deptno, but your output is by deptno and then job....

in any event, you have access to the grouping column, it is sortable.

ops$tkyte%ORA10GR2> select deptno, job, sum(sal),
  2         grouping(job), grouping(deptno)
  3    from scott.emp group by rollup(deptno,job)
  4    order by deptno, grouping(job), job, grouping(deptno)
  5  /

    DEPTNO JOB         SUM(SAL) GROUPING(JOB) GROUPING(DEPTNO)
---------- --------- ---------- ------------- ----------------
        10 CLERK           1300             0                0
        10 MANAGER         2450             0                0
        10 PRESIDENT       5000             0                0
        10                 8750             1                0
        20 ANALYST     95738.25             0                0
        20 CLERK           1900             0                0
        20 MANAGER         2975             0                0
        20            100613.25             1                0
        30 CLERK            950             0                0
        30 MANAGER         2850             0                0
        30 SALESMAN        5600             0                0
        30                 9400             1                0
                      118763.25             1                1

13 rows selected.




Just...

A reader, October 16, 2008 - 5:23 pm UTC

select deptno, job, sum(sal),
decode( grouping(job),1,'<===job'),
decode( grouping(deptno),1,'<===deptno')
from emp
group by rollup(job,deptno)
order by deptno, job
/

That doesn't work?
Tom Kyte
October 17, 2008 - 9:16 pm UTC

It does not if job is allowed to be NULL in general, no.

Rollup with ordered detail rows

tkyte_fan, October 16, 2008 - 8:15 pm UTC

That doesn't work well. It will cause the details rows and summary rows to move away from each other, if sorting is applied to the entire result set. This example may not demonstrate that, but imagine if dept 20 has a job by name "AAAClerk" (for example).

If that happens then there is no way for me to find out where that "AAAClerk" row belongs (which summary row).

I appreciate your response though. Tom it will be very helpful if you can assist here ? I have been trying hard to find the solution so far, but no luck yet.

Dan, October 17, 2008 - 12:08 pm UTC

If you order by deptno first, then by job, how would the details separate from the aggregate? Ordering by deptno will make all rows for the same deptno end up next to eachother, and then order by job inside of that.

You would get:
DEPTNO JOB SUM(SAL) DECODE( DECODE(GRO
---------- --------- ---------- ------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750 <===job
20 AAACLERK 1000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 11875 <===job
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400 <===job
30025 <===job <===deptno

Tom Kyte
October 18, 2008 - 9:42 pm UTC

4 order by deptno, grouping(job), job, grouping(deptno)


order by deptno (nulls go last by default)....

within each deptno, order the rows by whether job is grouped over or not (details come first). Within the details for JOB - sort them...

for the last deptno (the set of NULL deptnos) put the aggregate deptno last


grouping(deptno) could come first now that I look at this again as well. sort by whether deptno is grouped over or not - then within the deptno details by deptno and so on...

what about subquery factoring?

Radoslav Golian, October 19, 2008 - 1:10 pm UTC

SQL> create table tap_rtx_009(
  vplcode number,
  rated_flat_amount number,
  start_d_t date,
  actual_volume number
);

Table created.

SQL>insert into tap_rtx_009
select mod(trunc(dbms_random.value(1,1000)), 7),
       trunc(dbms_random.value(1,10)),
       sysdate + trunc(dbms_random.value(-10,10)),
       trunc(dbms_random.value(1,20))
from dual
connect by level < 1001;

1000 rows created.

SQL> commit;

Commit complete.


SQL>SELECT   vplcode, SUM(rated_flat_amount)/COUNT(*) amt 
FROM     tap_rtx_009
WHERE    vplcode = 2
GROUP BY vplcode,  start_d_t, actual_volume,
           rated_flat_amount
HAVING   COUNT(*) > 1

   VPLCODE        AMT
---------- ----------
         2          2
         2          9
         2          5


SQL>WITH main_select AS (
SELECT   vplcode, SUM(rated_flat_amount)/COUNT(*) amt 
FROM     tap_rtx_009
WHERE    vplcode = 2
GROUP BY vplcode,  start_d_t, actual_volume,
           rated_flat_amount
HAVING   COUNT(*) > 1)
SELECT vplcode, amt FROM (
SELECT 1, vplcode, amt
FROM main_select
UNION ALL
SELECT 2, NULL, sum(amt)
FROM main_select
ORDER BY 1)

   VPLCODE        AMT
---------- ----------
         2          2
         2          9
         2          5
                   16



SQL> drop table tap_rtx_009;

Table dropped.




Tom Kyte
October 21, 2008 - 11:40 am UTC

um, what about it exactly - not sure what you are addressing here or if you are asking a new question or what.


but no, i would not use a union all query of a with subquery to get what is easily retrieved with a group by rollup. Doesn't seem to make sense to do it that way. Not only is it harder to code - but I see multiple passes of the same data there.

A reader, November 22, 2013 - 9:07 am UTC