SQL*Plus User's Guide and Reference

COMPUTE

Purpose

Calculates and prints summary lines , using various standard computations, on subsets of selected rows , or lists all COMPUTE definitions. (For details on how to create summaries, see "Clarifying Your Report with Spacing and Summary Lines".)

Syntax

COMP[UTE] [function [LAB[EL] text] ... OF {expr|column|alias} ... ON {expr|column|alias|REPORT|ROW} ...]

Terms and Clauses

Refer to the following list for a description of each term or clause:

function ... Represents one of the functions listed in Table 6-2. If you specify more than one function, use spaces to separate the functions.

Function Computes Applies to Datatypes
AVG Average of non-null values NUMBER
COU[NT] Count of non-null values all types
MAX[IMUM] Maximum value NUMBER, CHAR, VARCHAR2 (VARCHAR)
MIN[IMUM] Minimum value NUMBER, CHAR, VARCHAR2 (VARCHAR)
NUM[BER] Count of rows all types
STD Standard deviation of non-null values NUMBER
SUM Sum of non-null values NUMBER
VAR[IANCE] Variance of non-null values NUMBER

Table 6 - 2. COMPUTE Functions

LAB[EL] text Defines the label to be printed for the computed value. If no LABEL clause is used, text defaults to the unabbreviated function keyword. If text contains spaces or punctuation, you must enclose it with single quotes. The label prints left justified and truncates to the column width or linesize, whichever is smaller. The maximum length of a label is 500 characters.
The label for the computed value appears in the break column specified. To suppress the label, use the NOPRINT option of the COLUMN command on the break column.
If you repeat a function in a COMPUTE command, SQL*Plus issues a warning and uses the first occurrence of the function.
With ON REPORT and ON ROW computations, the label appears in the first column listed in the SELECT statement. The label can be suppressed by using a NOPRINT column first in the SELECT statement. When you compute a function of the first column in the SELECT statement ON REPORT or ON ROW, then the computed value appears in the first column and the label is not displayed. To see the label, select a dummy column first in the SELECT list.
OF {expr|column|alias}... Specifies the column(s) or expression(s) you wish to use in the computation. (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) You must also specify these columns in the SQL SELECT command, or SQL*Plus will ignore the COMPUTE command.
If you use a SQL SELECT list alias, you must use the SQL alias in the COMPUTE command, not the column name. If you use the column name in this case, SQL*Plus will ignore the COMPUTE command.
If you do not want the computed values of a column to appear in the output of a SELECT command, specify that column in a COLUMN command with a NOPRINT clause. Use spaces to separate multiple expressions, columns, or aliases within the OF clause.
In the OF clause, you can refer to an expression or function reference in the SELECT statement by placing the expression or function reference in double quotes. Column names and aliases do not need quotes.
ON {expr|column|alias|REPORT|ROW} ... Specifies the event SQL*Plus will use as a break. (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) COMPUTE prints the computed value and restarts the computation when the event occurs (that is, when the value of the expression changes, a new ROW is fetched, or the end of the report is reached).
If multiple COMPUTE commands reference the same column in the ON clause, only the last COMPUTE command applies.
To reference a SQL SELECT expression or function reference in an ON clause, place the expression or function reference in quotes. Column names and aliases do not need quotes.

Enter COMPUTE without clauses to list all COMPUTE definitions.

Usage Notes

In order for the computations to occur, the following conditions must all be true:

One or more of the expressions, columns, or column aliases you reference in the OF clause must also be in the SELECT command.

The expression, column, or column alias you reference in the ON clause must occur in the SELECT command and in the most recent BREAK command.

If you reference either ROW or REPORT in the ON clause, also reference ROW or REPORT in the most recent BREAK command.

To remove all COMPUTE definitions, use the CLEAR COMPUTES command.

Examples

To subtotal the salary for the "clerk", "analyst", and "salesman" job classifications with a compute label of "TOTAL", enter

SQL> BREAK ON JOB SKIP 1
SQL> COMPUTE SUM LABEL 'TOTAL' OF SAL ON JOB
SQL> SELECT JOB, ENAME, SAL
  2  FROM EMP
  3  WHERE JOB IN ('CLERK', 'ANALYST', 'SALESMAN')
  4  ORDER BY JOB, SAL;

The following output results:

JOB       ENAME             SAL
--------- ---------- ----------
ANALYST   SCOTT            3000
          FORD             3000
*********            ----------
TOTAL                      6000

CLERK     SMITH             800
          JAMES             950
          ADAMS            1100
          MILLER           1300
*********            ----------
TOTAL                      4150

SALESMAN  WARD             1250
          MARTIN           1250
          TURNER           1500
          ALLEN            1600
*********            ----------
TOTAL                      5600

To calculate the total of salaries less than 1,000 on a report, enter

SQL> COMPUTE SUM OF SAL ON REPORT
SQL> BREAK ON REPORT
SQL> COLUMN DUMMY HEADING ''
SQL> SELECT '   ' DUMMY, SAL, EMPNO
  2  FROM EMP
  3  WHERE SAL < 1000
  4  ORDER BY SAL;

The following output results:

           SAL       EMPNO
--- ---------- -----------
           800        7369
           950        7900
    ----------
sum       5350

To compute the average and maximum salary for the accounting and sales departments, enter

SQL> BREAK ON DNAME SKIP 1
SQL> COMPUTE AVG LABEL 'Dept Average' -
>            MAX LABEL 'Dept Maximum' -
>       OF SAL ON DNAME
SQL> SELECT DNAME, ENAME, SAL
  2  FROM DEPT, EMP
  3  WHERE DEPT.DEPTNO = EMP.DEPTNO
  4  AND DNAME IN ('ACCOUNTING', 'SALES')
  5  ORDER BY DNAME;

The following output results:

DNAME          ENAME             SAL
-------------- ---------- ----------
ACCOUNTING     CLARK            2450
               KING             5000
               MILLER           1300
**************            ----------
Dept Average              2916.66667
Dept Maximum                    5000

SALES          ALLEN            1600
               WARD             1250
               JAMES             950
               TURNER           1500
               MARTIN           1250
               BLAKE            2850
**************            ----------
Dept Average              1566.66667
Dept Maximum                    2850

To compute the sum of salaries for departments 10 and 20 without printing the compute label:

SQL> COLUMN DUMMY NOPRINT
SQL> COMPUTE SUM OF SAL ON DUMMY
SQL> BREAK ON DUMMY SKIP 1
SQL> SELECT DEPTNO DUMMY, DEPTNO, ENAME, SAL
  2  FROM EMP
  3  WHERE DEPTNO <= 20
  4  ORDER BY DEPTNO;

SQL*Plus displays the following output:

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450
        10 MILLER           1300
                      ----------
                            8750

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

If, instead, you do not want to print the label, only the salary total at the end of the report:

SQL> COLUMN DUMMY NOPRINT
SQL> COMPUTE SUM OF SAL ON DUMMY
SQL> BREAK ON DUMMY
SQL> SELECT NULL DUMMY, DEPTNO, ENAME, SAL
  2  FROM EMP
  3  WHERE DEPTNO <= 20
  4  ORDER BY DEPTNO;

SQL*Plus displays the following output:

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450
        10 MILLER           1300
        20 JONES            2975
        20 FORD             3000
        20 SMITH             800
        20 SCOTT            3000
        20 ADAMS            1100
                      ----------
                           19625

*Function*	*Computes*	*Applies to Datatypes*
`AVG`	Average of non-null values	NUMBER
`COU[NT]`	Count of non-null values	all types
`MAX[IMUM]`	Maximum value	NUMBER, CHAR, VARCHAR2 (VARCHAR)
`MIN[IMUM]`	Minimum value	NUMBER, CHAR, VARCHAR2 (VARCHAR)
`NUM[BER]`	Count of rows	all types
`STD`	Standard deviation of non-null values	NUMBER
`SUM`	Sum of non-null values	NUMBER
`VAR[IANCE]`	Variance of non-null values	NUMBER

`LAB[EL] text`	Defines the label to be printed for the computed value. If no LABEL clause is used, text defaults to the unabbreviated function keyword. If text contains spaces or punctuation, you must enclose it with single quotes. The label prints left justified and truncates to the column width or linesize, whichever is smaller. The maximum length of a label is 500 characters.
	The label for the computed value appears in the break column specified. To suppress the label, use the NOPRINT option of the COLUMN command on the break column.
	If you repeat a function in a COMPUTE command, SQL*Plus issues a warning and uses the first occurrence of the function.
	With ON REPORT and ON ROW computations, the label appears in the first column listed in the SELECT statement. The label can be suppressed by using a NOPRINT column first in the SELECT statement. When you compute a function of the first column in the SELECT statement ON REPORT or ON ROW, then the computed value appears in the first column and the label is not displayed. To see the label, select a dummy column first in the SELECT list.
`OF {expr\|column\|alias}...`	Specifies the column(s) or expression(s) you wish to use in the computation. (column cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) You must also specify these columns in the SQL SELECT command, or SQL*Plus will ignore the COMPUTE command.
	If you use a SQL SELECT list alias, you must use the SQL alias in the COMPUTE command, not the column name. If you use the column name in this case, SQL*Plus will ignore the COMPUTE command.
	If you do not want the computed values of a column to appear in the output of a SELECT command, specify that column in a COLUMN command with a NOPRINT clause. Use spaces to separate multiple expressions, columns, or aliases within the OF clause.
	In the OF clause, you can refer to an expression or function reference in the SELECT statement by placing the expression or function reference in double quotes. Column names and aliases do not need quotes.
`ON {expr\|column\|alias\|REPORT\|ROW} ...`	Specifies the event SQLPlus will use as a break. (column* cannot have a table or view appended to it. To achieve this, you can alias the column in the SQL statement.) COMPUTE prints the computed value and restarts the computation when the event occurs (that is, when the value of the expression changes, a new ROW is fetched, or the end of the report is reached).
	If multiple COMPUTE commands reference the same column in the ON clause, only the last COMPUTE command applies.
	To reference a SQL SELECT expression or function reference in an ON clause, place the expression or function reference in quotes. Column names and aliases do not need quotes.