Skip to Main Content
  • Questions
  • Analytical functions - grand total, sub totals

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: April 11, 2004 - 12:00 pm UTC

Last updated: March 19, 2007 - 3:43 pm UTC

Version: 9.0.2

Viewed 10K+ times! This question is

You Asked

Tom

I'm trying to write a query which gives a grand total, as well as sub totals for a couple of columns.


SQL> ed
Wrote file afiedt.buf

1 select ename,deptno, mgr, sum(sal)
2 from emp
3* group by rollup(ename,deptno, mgr)
SQL> /

ENAME DEPTNO MGR SUM(SAL)
---------- ---------- ---------- ----------
FORD 20 7566 3000
FORD 20 3000
FORD 3000
KING 10 5000
KING 10 5000
KING 5000
WARD 30 7698 1250
WARD 30 1250
WARD 1250
ADAMS 20 7788 1100
ADAMS 20 1100

ENAME DEPTNO MGR SUM(SAL)
---------- ---------- ---------- ----------
ADAMS 1100
ALLEN 30 7698 1600
ALLEN 30 1600
ALLEN 1600
BLAKE 30 7839 2850
BLAKE 30 2850
BLAKE 2850
CLARK 10 7839 2450
CLARK 10 2450
CLARK 2450
JAMES 30 7698 950

ENAME DEPTNO MGR SUM(SAL)
---------- ---------- ---------- ----------
JAMES 30 950
JAMES 950
JONES 20 7839 2975
JONES 20 2975
JONES 2975
SCOTT 20 7566 3000
SCOTT 20 3000
SCOTT 3000
SMITH 20 7902 800
SMITH 20 800
SMITH 800

ENAME DEPTNO MGR SUM(SAL)
---------- ---------- ---------- ----------
MARTIN 30 7698 1250
MARTIN 30 1250
MARTIN 1250
MILLER 10 7782 1300
MILLER 10 1300
MILLER 1300
TURNER 30 7698 1500
TURNER 30 1500
TURNER 1500
29025

43 rows selected.



I want to see the ename in the output, but I should break on the ename, and should not see ename repeated. I just want the grand total, and sub totals for each MGR and DEPTNO with label 'sub total'.


How can I achieve this.


and Tom said...

well, I fail to see how you can have the mgr "subtotal" given that a manager could manage people in different DEPTNOS so if we sorted by DEPTNO/MGR -- a mgr would have people in different deptnos (where would that subtotal go???)

MGR=7839 fits that category. No way to get a "sub total" for a mgr by deptno and have that be the subtotal for the mgr in this case using simple aggregation since the MGR spans deptnos. (only if a mgr managed people IN a deptno would this work using aggregation)

But, we can get all of your required records:

group by grouping sets( (ename,deptno,mgr), (deptno), (mgr), () )
^^^^^^^^^^^^^^^^^^ the details
^^^^^^^^ by deptno
^^^^^ by mgr
^^ grand total



This gives you a feel for what you can do with grouping/aggregates -- but only if MGRS did not occurr across DEPTNOS would you be able to achieve your stated goal:

scott@ORA9IR2> select decode( bin_to_num( grouping( ename),grouping(deptno), grouping(mgr) ),
2 0, 'detail',
3 5, 'by deptno',
4 6, 'by mgr',
5 7, 'grand total' ) what,
6 ename,
7 deptno,
8 mgr,
9 sum(sal) ,
10 grouping(ename) gename,
11 grouping(deptno) gdeptno,
12 grouping(mgr) gmgr
13 from emp
14 group by grouping sets( (ename,deptno,mgr), (deptno), (mgr), () )
15 order by decode( bin_to_num( grouping( ename),grouping(deptno), grouping(mgr) ), 6, 1, 0 ),
16 deptno NULLS LAST, grouping( DEPTNO ) desc, ename
17 /

WHAT ENAME DEPTNO MGR SUM(SAL) GENAME GDEPTNO GMGR
----------- ------ ------ ----- ---------- ---------- ---------- ----------
detail CLARK 10 7839 2450 0 0 0
detail KING 10 5000 0 0 0
detail MILLER 10 7782 1300 0 0 0
by deptno 10 8750 1 0 1
detail ADAMS 20 7788 1100 0 0 0
detail FORD 20 7566 3000 0 0 0
detail JONES 20 7839 2975 0 0 0
detail SCOTT 20 7566 3000 0 0 0
detail SMITH 20 7902 800 0 0 0
by deptno 20 10875 1 0 1
detail ALLEN 30 7698 1600 0 0 0
detail BLAKE 30 7839 2850 0 0 0
detail JAMES 30 7698 950 0 0 0
detail MARTIN 30 7698 1250 0 0 0
detail TURNER 30 7698 1500 0 0 0
detail WARD 30 7698 1250 0 0 0
by deptno 30 9400 1 0 1
grand total 29025 1 1 1
by mgr 7566 6000 1 1 0
by mgr 7698 6550 1 1 0
by mgr 7782 1300 1 1 0
by mgr 7788 1100 1 1 0
by mgr 7839 8275 1 1 0
by mgr 7902 800 1 1 0
by mgr 5000 1 1 0

25 rows selected.




Rating

  (12 ratings)

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

Comments

What is the best way to do this..

A reader, May 28, 2004 - 7:11 pm UTC


DROP TABLE TEST;
CREATE TABLE TEST( DAY DATE, VOLUME NUMBER);
INSERT INTO TEST VALUES ( SYSDATE, 100);
INSERT INTO TEST VALUES ( SYSDATE -1, 0);
INSERT INTO TEST VALUES ( SYSDATE +1 , 200);
COMMIT;


For a given day , I need to select volume, only if the previous day volume and the current day volume are not zero. What is the best way to accomplish this.

Tom Kyte
May 28, 2004 - 8:15 pm UTC

what is the "current day volumne" if that is the same as the "given day" then


select volume
from test
where day = to_date(:given_day, 'format....' )
and volume <> 0
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 );

A reader, May 28, 2004 - 11:33 pm UTC

Dude

Current day is sysdate.
Previous day is sysdate -1

Tom Kyte
May 29, 2004 - 11:11 am UTC

dude to you, when statement starts with:

for a given day

and then goes into "current day", the question would be -- is given day current day, if not what then is given day.......




A reader, May 28, 2004 - 11:41 pm UTC

Tom Dude

How will the query look if we have a group by on date. Can we have an exists in the having clause.



Tom Kyte
May 29, 2004 - 11:12 am UTC

can you have an exists, sure.

no clue as to where group by would come into play for this table as the example is drawn out though.

having and exists

Lauren Hill, May 29, 2004 - 3:09 pm UTC

Interesting though,

TOM, I observe that it exists might not be very useful in the having clause. Instead why not just using the exists clause in the where, and just use the group by with out having.

In the given discussion

I guess what the reader is asking is

select day,sum(volume)
from test
where day = to_date(:given_day, 'format....' )
and volume <> 0
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )

group by day

or this can be written as


select day,sum(volume)
from test
where day = to_date(:given_day, 'format....' )
and volume <> 0
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )

group by day


Now instead doing the

and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )

in the where clause , how can we do it in the having clause after group by, and what is difference in both the approaches.

HAVING exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )

Thanks

Tom Kyte
May 30, 2004 - 10:34 am UTC

(i've given up in most cases trying to "guess" what they mean.....)


but yes, the exists can be in either or location

A reader, June 01, 2004 - 11:35 am UTC

select volume
from test
where day = to_date(:given_day, 'format....' )
and volume <> 0
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 );

Tom, in the above case, the actual requirement is

Return the row if volume for either of the days i.e. :given_day or to_date( :given_day, 'format...' ) -1 is not equal to zero. Do not return the row, if both of them are zero.

In the query you wrote, the row will not be returned if volume for the :given_day is zero but the volume for the to_date( :given_day, 'format...' ) -1 is not equal to zero.

My solution is the following, please verify


select volume
from test
where day = to_date(:given_day, 'format....' )
and (volume <> 0 or volume = 0)
and exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )


Tom Kyte
June 01, 2004 - 3:14 pm UTC

the query I wrote was for this requirement:

... only if the previous day volume and the current day volume are not zero. ....


just change my AND to an OR

select volume
from test
where day = to_date(:given_day, 'format....' )
and ( volume <> 0
OR
exists ( select null
from test
where day = to_date( :given_day, 'format...' ) -1
and volume <> 0 )
)



How should we achieve the output we are looking for.

Otn, May 02, 2005 - 3:49 pm UTC

create table t ( start_date date, end_date date, value number)
/

insert into t values( '01-AUG-2005', '31-AUG-2005', 50)
/

insert into t values( '01-SEP-2005', '30-SEP-2005', 50)
/
Insert into t values( '01-OCT-2005', '31-OCT-2005', 60)
/
insert into t values( '01-NOV-2005', '30-NOV-2005', 50)
/
COMMIT
/


SQL> SET LINESIZE 200
SQL> SELECT * FROM T;

START_DAT END_DATE       VALUE
--------- --------- ----------
01-AUG-05 31-AUG-05         50
01-SEP-05 30-SEP-05         50
01-OCT-05 31-OCT-05         60
01-NOV-05 30-NOV-05         50




The output we are looking for is

01-AUG-05      30-SEP-05      50
01-OCT-05      31-OCT-05      60
01-NOV-05      30-NOV-05      50


IF group by value the output is coming out as below, which is wrong because the first row shows a range which includes october.


01-AUG-05  -    30-NOV-05      50
01-OCT-05  -    31-OCT-05      60


IF grouped by begin and end date, it will return all the 4 rows, which we want to avoid.

How should we achieve the output we are looking for.
 

Tom Kyte
May 03, 2005 - 7:27 am UTC

see
https://www.oracle.com/technetwork/issue-archive/2014/14-mar/o24asktom-2147206.html
analytics to the rescue (in 10g we could use last_value with ignore nulls instead of the max() trick to carry down)...


ops$tkyte@ORA9IR2> select min(start_date), max(end_date), min(value)
  2    from (
  3  select t.*, max(grp) over (order by start_date) grp2
  4    from (
  5  select t.*,
  6         case when nvl(lag(value) over (order by start_date),value*-1) <> value
  7                  then row_number() over (order by start_date)
  8                  end grp
  9    from t
 10         ) t
 11             )
 12   group by grp2
 13   order by 1;
 
MIN(START MAX(END_D MIN(VALUE)
--------- --------- ----------
01-AUG-05 30-SEP-05         50
01-OCT-05 31-OCT-05         60
01-NOV-05 30-NOV-05         50
 

A reader, May 09, 2005 - 11:42 am UTC

I have a scenario where the value will not be a number all the time, some times the value will be a string and other times the value will be a number. How should I handle that.

'Value' is the data you are seeing under the column value above. In the given example it is a number, but it has to be a varchar2 column.

Thanks in advance.

Tom Kyte
May 09, 2005 - 12:22 pm UTC

max works -- which one of the values from the range did you want to display??

value*-1

A reader, May 12, 2005 - 4:22 pm UTC

In the above sql what is the purpose of value*-1?

Tom Kyte
May 13, 2005 - 8:29 am UTC

when nvl(lag(value) over (order by start_date),value*-1) <> value


if the lag(value) is not null then we have:

when lag-value <> value


when we have lag-value being null we would have

when NULL <> value

which is not true or false, but we'd like false so

when -1*value <> value

assuming value (undocumented assumption on my part there) is not null we'd get "true"

To get previous day

A reader, May 20, 2005 - 3:01 pm UTC

Hi Tom,

If I do a
'select sysdate from dual;'
I am getting

SYSDATE
-------------------
05-20-2005 14:57:17

When I do 'select sysdate-1 from dual;'
I get

SYSDATE-1
-------------------
05-19-2005 14:57:22

What should I do if I want to get 05-19-2005 00:00:00
and 05-19-2005 23:59:59 as results.

Thanks.

Tom Kyte
May 20, 2005 - 6:44 pm UTC

select trunc(sysdate-1), trunc(sysdate)-1/24/60/60 from dual

Top 10 Analytical functions

A reader, March 11, 2007 - 6:47 pm UTC

Tom

Based on all the analytics questions you have answered on ASKTOM, what are the top 12 analytical functions, you have used.

Can you please list them out. Thanks in advance.
Tom Kyte
March 12, 2007 - 8:22 pm UTC

from a talk on analytics that I give:

LAG/LEAD (look back/forwards)
FIRST/LAST or MIN/MAX
ROW_NUMBER, RANK, DENSE_RANK, RATIO_TO_REPORT
AVG, SUM
NTILE
PERCENTILE_CONT/DISC

Great Answer. How to find the count(distinct) of column combination?

Prem, March 19, 2007 - 2:18 pm UTC

Tom,

Can I use the analytical function such as CUBE or ROLLUP to get the following?

create table t1 (col1 number, col3 number, col4 number, col6 number);

insert into t1 values (1, 10, 20, 30);
insert into t1 values (2, 20, 20, 30);
insert into t1 values (1, 20, 20, 20);
insert into t1 values (1, 10, 20, 30);
insert into t1 values (3, 30, 40, 60);
insert into t1 values (3, 30, 50, 50);
insert into t1 values (1, 10, 20, 30);

How would I find the count of distinct values of col1 alone, col1&col3 alone and col1&col3&col4 alone using a single query?

I would like to see the result as below (column title is not required),

col1 col1&col3 col1&col3&col4
------ -------------- --------------
3 4 5

col1 - I have three distinct values 1, 2, and 3
col1||col2 - I have 4 distinct values (1,10),(2,20), (1,20),(3,30)

Thank you!!!
Tom Kyte
March 19, 2007 - 3:43 pm UTC

ops$tkyte%ORA10GR2> select count(distinct col1) c1,
  2         count(distinct col1||'/'||col3) c1c3,
  3         count(distinct col1||'/'||col3||'/'||col4) c1c3c4
  4    from t1;

        C1       C1C3     C1C3C4
---------- ---------- ----------
         3          4          5


Thank you!! I didn't think of the straight method

Prem, March 19, 2007 - 8:35 pm UTC

Thanks Tom for your response