OracleBlog

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