iSQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88826-01 |
|
Command Reference, 12 of 38
COMP[UTE] [function [LAB[EL] text] ...
OF {expr|column|alias} ...
ON {expr|column|alias|REPORT|ROW} ...]
Calculates and prints summary lines, using various standard computations, on subsets of selected rows. It also lists all COMPUTE definitions.
Refer to the following list for a description of each term or clause:
function ...
Represents one of the functions listed in Table 5-2. If you specify more than one function, use spaces to separate the functions.
COMPUTE command functions are always executed in the sequence AVG, COUNT, MINIMUM, MAXIMUM, NUMBER, SUM, STD, VARIANCE, regardless of their order in the COMPUTE command.
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. You must place single quotes around text containing spaces or punctuation. The label prints left justified and truncates to the column width or linesize, whichever is smaller. The maximum label length 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 "account manager" and "salesman" job classifications with a compute label of "TOTAL", enter
BREAK ON JOB_ID SKIP 1; COMPUTE SUM LABEL 'TOTAL' OF SALARY ON JOB_ID; SELECT JOB_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID IN ('AC_MGR', 'SA_MAN') ORDER BY JOB_ID, SALARY; JOB_ID LAST_NAME SALARY ---------- ------------------------- ---------- AC_MGR Higgins 12000 ********** ---------- TOTAL 12000 SA_MAN Zlotkey 10500 Cambrault 11000 Errazuriz 12000 Partners 13500 Russell 14000 ********** ---------- TOTAL 61000 6 rows selected.
To calculate the total of salaries greater than 12,000 on a report, enter
COMPUTE SUM OF SALARY ON REPORT BREAK ON REPORT COLUMN DUMMY HEADING '' SELECT ' ' DUMMY, SALARY, EMPLOYEE_ID FROM EMP_DETAILS_VIEW WHERE SALARY > 12000 ORDER BY SALARY; SALARY EMPLOYEE_ID --- ---------- ----------- 13000 201 13500 146 14000 145 17000 101 17000 102 24000 100 ---------- sum 98500 6 rows selected.
To calculate the average and maximum salary for the executive and accounting departments, enter
BREAK ON DEPARTMENT_NAME SKIP 1 COMPUTE AVG LABEL 'Dept Average' - MAX LABEL 'Dept Maximum' - OF SALARY ON DEPARTMENT_NAME SELECT DEPARTMENT_NAME, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_NAME IN ('Executive', 'Accounting') ORDER BY DEPARTMENT_NAME; DEPARTMENT_NAME LAST_NAME SALARY ------------------------------ ------------------------- ---------- Accounting Higgins 12000 Gietz 8300 ****************************** ---------- Dept Average 10150 Dept Maximum 12000 Executive King 24000 Kochhar 17000 De Haan 17000 ****************************** ---------- Dept Average 19333.3333 Dept Maximum 24000
To sum salaries for departments <= 20 without printing the compute label, enter
COLUMN DUMMY NOPRINT COMPUTE SUM OF SALARY ON DUMMY BREAK ON DUMMY SKIP 1 SELECT DEPARTMENT_ID DUMMY, DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID <= 20 ORDER BY DEPARTMENT_ID; DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 10 Whalen 4400 ---------- 4400 20 Hartstein 13000 20 Fay 6000 ---------- 19000
To total the salary at the end of the report without printing the compute label, enter
COLUMN DUMMY NOPRINT COMPUTE SUM OF SALARY ON DUMMY BREAK ON DUMMY SELECT NULL DUMMY, DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID <= 30 ORDER BY DEPARTMENT_ID; DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 10 Whalen 4400 20 Hartstein 13000 20 Fay 6000 30 Raphaely 11000 30 Khoo 3100 30 Baida 2900 30 Tobias 2800 30 Himuro 2600 30 Colmenares 2500 ---------- 48300 9 rows selected.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|