Name
COMPUTE
Synopsis
COMP[UTE] [{AVG | COU[NT] | MAX[IMUM] |
MIN[IMUM] | NUM[BER] | STD | SUM |
VAR[IANCE]}...
[LABEL label_text]
OF column_name...
ON {group_column_name |
ROW | REPORT}...]The COMPUTE command defines summary calculations needed in a report. You can use COMPUTE in conjunction with BREAK to calculate and print column totals, averages, minimum and maximum values, and so forth. These calculations are performed by SQL*Plus as the report runs. COMPUTE is a complex command and must be used in conjunction with the BREAK command in order to get results. For example:
BREAK ON project_id COMPUTE SUM LABEL "Totals" OF hours_logged - ON project_id BREAK ON project_id ON employee_id COMPUTE SUM OF hours_logged - ON project_id, employee_id
Issuing COMPUTE with no parameters causes SQL*Plus to list all currently defined computations.
Parameters
- AVG
Computes the average of all non-null values for a numeric column.
- COU[NT]
Computes the total number of non-null values for a column.
- MAX[IMUM]
Computes the maximum value returned for a column. Applies to columns of type NUMBER, CHAR, VARCHAR2, NCHAR, and NVARCHAR2.
- MIN[IMUM]
Computes the minimum value returned for a column. Applies to columns of type NUMBER, CHAR, VARCHAR2, NCHAR, and NVARCHAR2.
- NUM[BER]
Performs a function similar to COUNT but computes the number of all values, including nulls.
- STD
Computes the standard deviation of all non-null values for a numeric column.
- SUM
Computes the sum of all non-null values for a numeric column.
- VAR[IANCE]
Computes the variance of all non-null values for a numeric column.
- LABEL
Allows you to specify a label for the computed value. If possible, this label will be printed to the left of the computed value.
- label_text
Is the text you want to use as a label when the computed value is printed.
- column_name
Is the name of the column you are summarizing. If it’s a computed column, the expression is the name. If your SELECT statement aliases the column, you must use that alias name here.
- group_column_name
Causes SQL*Plus to restart the calculation every time this column changes.
- ROW
Causes the computation to be performed once for each row returned by the query.
- REPORT
Causes the computation to be performed at the end of the report and to include values from all rows. REPORT is used for grand totals.