Shape Compute Command

See Also

A Shape COMPUTE command executes an aggregate function on the rows of the child Recordset to generate a parent Recordset, then assigns the child Recordset to the Value property of Field objects in the generated parent Recordset.

Syntax

"SHAPE {child-command} [[AS] table-alias]

COMPUTE aggregate-command-field-list

[BY grp-field-list]"

Description of parts

The parts of this command are:

child-command—A query command that returns a Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be Structured Query Language (SQL), although ADO doesn't require any particular query language.
table-alias—An alias used to refer to the Recordset returned by the child-command.
aggregate-command-field-list—A list of fields operated on by an aggregate function. The Recordset may be referred to by its table-alias in the aggregate function. You can also create a column to use as you desire, with the NEW operator.

Aggregate Function	Description
SUM(<alias>.<child-field-name>)	Calculates the sum of all values in the specified field.
AVG(<alias>.<child-field-name>)	Calculates the average of all values in the specified field.
MAX(<alias>.<child-field-name>)	Calculates the maximum value in the specified field.
MIN(<alias>.<child-field-name>)	Calculates the minimum value in the specified field.
COUNT(<alias>[.<child-field-name>])	Counts the number of rows in the specified field.
STDEV(<alias>.<child-field-name>)	Calculates the standard deviation in the specified field.
ANY(<alias>.<child-Field-name>)	The value of a column (where the value of the column is the same for all rows).
CALC(expression)	Calculates an arbitrary expression, but only on the current row.
NEW (field type [(width \| scale [,precision])]	Adds an empty column of the specified type to the Recordset.

grp-field-list—A list of columns specifying the order of rows in the child Recordset. If the BY clause is not specified, then only the result of the aggregate function will be returned. If the BY clause is specified, the child Recordset will be appended to the generated parent Recordset.

Operation

The Client Cursor Engine will issue the child-command to the provider, which will return a child Recordset.

The COMPUTE clause specifies an aggregate operation to be executed on specified columns (aggregate-command-field-list) of the child Recordset, such as summing all the values in a column, or finding the maximum value in a column. The aggregate operation creates a parent Recordset.

If there is no BY clause, then the Shape command concludes. If there is a BY clause, the child Recordset will be appended to the parent Recordset. The rows of the child Recordset will be arranged in groups as specified in the grp-field-list.

For example, assume you have a table, Demographics, consisting of State, City, and Population fields (the population figures are solely for illustration).

State	City	Population
WA	Seattle	700,000
OR	Medford	200,000
OR	Portland	600,000
CA	Los Angeles	900,000
CA	San Diego	400,000
WA	Tacoma	500,000
OR	Corvallis	300,000

Now, issue this Shape command:

    rst.Open    "SHAPE  {select * from demographics} 
               COMPUTE (SUM(population)) AS chapter 
               BY state", 
               connection

This command opens a parent Recordset. Because you specified the BY clause, a hidden column was appended, with references to Recordset objects that provide detail for each row in the parent Recordset.

The child Recordset detail rows will be grouped, but not in any particular order. That is, the groups will not automatically be in alphabetical or numerical order.

An arbitrary name will be generated for the appended column.

You can now navigate the opened parent Recordset, and access the child detail Recordset objects. See Accessing Rows in a Hierarchical Recordset.

Resultant Parent and Child Detail Recordsets

Parent

State	SUM (Population)	(Appended field)
CA	1,300,000	Reference to child1
WA	1,200,000	Reference to child2
OR	1,100,000	Reference to child3

Child1

State	City	Population
CA	Los Angeles	900,000
CA	San Diego	400,000

Child2

State	City	Population
WA	Seattle	700,000
WA	Tacoma	500,000

Child3

State	City	Population
OR	Medford	200,000
OR	Portland	600,000
OR	Corvallis	300,000