Wednesday, August 24, 2005

COMPUTE

Let's say you wanted to write a query that contained an aggregate (eg: sum, min, max, count). No problem, you can use 'GROUP BY'.

But suppose you wanted to write a query that was a report containing both the aggregate and the detail. That makes it trickier.

You may even know how to do it in other databases:

http://sybasease.blogspot.com/2005/08/compute.html

There is a simple way of accomplishing this in SQL*Plus. SQL*Plus includes a function of the same name (COMPUTE) that will do the formatting for you.

In order to use it, you also have to use 'BREAK'. Let's take a very brief look at it. BREAK, like COMPUTE, is an SQL*Plus command that you would issue at any time prior to your query. For example, the following BREAK command will remove all values in DEPTNO if they are the same as the preceding value, and skip 1 line after each grouping.

scott@Robert> BREAK ON deptno SKIP 1 NODUPLICATES
scott@Robert> SELECT ename, sal, deptno
2 FROM emp
3 ORDER BY deptno;

ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
VOLLMAN 5000
MILLER 1300

SMITH 800 20
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975

ALLEN 1600 30
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250


Now let's add our COMPUTE command. This one will calculate the subtotal of salary, just like it would using GROUP BY. The advantage here is that its in the same table.

scott@Robert> COMPUTE SUM LABEL subtotal OF sal ON deptno
scott@Robert> SELECT ename, sal, deptno FROM emp ORDER BY deptno;

ENAME             SAL     DEPTNO
---------- ---------- ----------
CLARK 2450 10
VOLLMAN 5000
MILLER 1300
---------- **********
8750 subtotal

SMITH 800 20
ADAMS 1100
FORD 3000
SCOTT 3000
JONES 2975
---------- **********
10875 subtotal

ALLEN 1600 30
BLAKE 2850
MARTIN 1250
JAMES 950
TURNER 1500
WARD 1250
---------- **********
9400 subtotal


Naturally, this also works with other aggregate functions like SUM, MIN, MAX, AVG, STD, VARIANCE, COUNT, NUMBER.

For reference, check out the SQL*Plus User's Guide:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a90842.pdf

You want chapters 7 and 13:
7 Formatting SQL*Plus Reports
13 SQL*Plus Command Reference

UPDATE: Actually there's a simple way without SQL*Plus, by using GROUPING, see Tom Kyte's Comment. Here is his example of my Oracle9 environment.

scott@Robert> SELECT DECODE(GROUPING_ID(ename), 1, 'Subtotal' ) tag
2 ename, deptno, SUM(sal)
3 FROM emp
4 GROUP BY GROUPING SETS( (ename,deptno), (deptno) );


TAG      ENAME          DEPTNO   SUM(SAL)
-------- ---------- ---------- ----------
CLARK 10 2450
MILLER 10 1300
VOLLMAN 10 5000
Subtotal 10 8750
FORD 20 3000
ADAMS 20 1100
JONES 20 2975
SCOTT 20 3000
SMITH 20 800
Subtotal 20 10875
WARD 30 1250
ALLEN 30 1600
BLAKE 30 2850
JAMES 30 950
MARTIN 30 1250
TURNER 30 1500
Subtotal 30 9400


Read about GROUPING, GROUPING_ID and GROUPING SETS in SQL Reference:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf

Comments:
scott@ORA10GR1> select decode( grouping_id(ename), 1, 'Subtotal' ) tag,
2 ename, deptno, sum(sal)
3 from emp
4 group by grouping sets( (ename,deptno), (deptno) )
5 /

TAG ENAME DEPTNO SUM(SAL)
-------- ---------- ---------- ----------
king 10 5000
clark 10 2450
miller 10 1300
Subtotal 10 8750
ford 20 3000
adams 20 1100
jones 20 2975
scott 20 3000
smith 20 800
Subtotal 20 10875
ward 30 1250
allen 30 1600
blake 30 2850
james 30 950
martin 30 1250
turner 30 1500
Subtotal 30 9400

17 rows selected.
 
Darn fast, Tom! Was going to have a crack at it as well with the DW extensions but you beat me to it.
One of the least explored areas of Oracle but worth its weight in gold.
 
I didn't look at Tom's answer (honest!) but my reply consists of simply GROUPING functions and ROLLUP. Are these now deprecated in place of GROUPING_ID and GROUP SETS??

select decode(grouping(ename),1,'',ename) as ename,
sum(sal) as sal,
decode(grouping(ename),1,'Sub total',deptno) as deptno
from emp
group by rollup(ename), deptno;

ENAME SAL DEPTNO
-------------------- ---------- ----------------------------------------
CLARK 2450 10
MILLER 1300 10
VOLLMAN 5000 10
8750 Sub total
FORD 3000 20
ADAMS 1100 20
JONES 2975 20
SCOTT 3000 20
SMITH 800 20
10875 Sub total
WARD 1250 30
ALLEN 1600 30
BLAKE 2850 30
JAMES 950 30
MARTIN 1250 30
TURNER 1500 30
9400 Sub total


BTW mine looks more like Robert's SQL*Plus output :)
 
I was wondering when someone would mention ROLLUP. :)

I love the way you even changed your emp table to make me the President, too!!

I think the grand moral of the story is to consider GROUPING, ROLLUP, GROUPING_ID, and GROUP SETS for situations where you need details with aggregates.
 
No love for group by cube. In 8i, without grouping sets and grouping id, group by cube and a having clause present an option.
 
Hi there!
I just answered a similar question on otn a few minutes ago:
Re: Display Grand Total on the same line.See output

without group by, without subquery, I used decode and analytics.

Kindest regards
 
This comment has been removed by a blog administrator.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?