|
|
|
COMPUTE
Syntax
COMPUTE
[ { AVG | MAX | MIN | SUM | COUNT }
OF column_name
IN variable_name
ON break_spec ] ;
break_spec::
{ column_name | ROW | PAGE | REPORT }
Description
Performs aggregate function computations on column values for the specified set of rows, and assigns the results to a variable. DISPLAY statements can then refer to the variable to display its value.
COMPUTE statements have no effect until you issue a BREAK statement with the same break_spec.
Issuing the COMPUTE statement without any arguments displays the currently-set COMPUTE specifications, if any.
Arguments
AVG | MAX | MIN | SUM | COUNT
The function to apply to values of column_name. The functions AVG, MAX, MIN, and SUM can be used only when the column is numeric. The function COUNT can be used for any column type.
column_name
The column whose value is to be computed. The column specified in column_name must also be included in the select list of the query. If column_name is not also included in the select list, it has no effect.
variable_name
Specifies the name of the variable where the computed value is stored. ISQL issues an implicit DEFINE statement for variable_name and assigns the variable a value of zero. During query processing, the value of variable_name changes as ISQL encounters the specified breaks.
break_spec
Specifies the set of rows after which ISQL processes the COMPUTE statement. A COMPUTE statement has no effect until you issue a corresponding BREAK statement. See the description of the BREAK statement in "BREAK" for details.
Examples
The following example computes the number of items ordered by each customer.
ISQL> break on customer_name
ISQL> display col 5 "Number of orders placed by", customer_name, "=", n_ord on customer_name
ISQL> compute count of order_id in n_ord on customer_name;
ISQL> select c.customer_name, o.order_id from customers c, orders o
where o.customer_id = c.customer_id;
CUSTOMER_NAME ORDER_ID
------------- --------
Sports Cars Inc. 1
Sports Cars Inc. 2
Number of orders placed by Sports Cars Inc.
= 2
Mighty Bulldozer Inc. 3
Mighty Bulldozer Inc. 4
Number of orders placed by Mighty Bulldozer Inc.
= 2
.
.
.
