Release 8.1.5
A66736-01
 Library |
 Product |
 Contents |
 Index |
| SQL*Plus User's Guide and Reference Release 8.1.5 A66736-01 |
|
Command Reference, 15 of 52
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" in Chapter 4.)
COMP[UTE] [function [LAB[EL] text] ... OF {expr|column|alias} ... ON {expr|column|alias|REPORT|ROW} ...]
Refer to the following list for a description of each term or clause:
function ...
Represents one of the functions listed in Table 8-2. If you specify more than one function, use spaces to separate the 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} ...
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.
In order for the computations to occur, the following conditions must all be true:
To remove all COMPUTE definitions, use the CLEAR COMPUTES command.
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
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|