Aggregate Operators

Aggregate operators perform calculations on values obtained from a collection, sequence, or group of objects.

Operator | Description | Usage | Unary Operand (op1) | Result Type | See Also |

COUNT | Returns the number of elements in a collection or sequence. | COUNT(op1) | Collection or sequence of any type | Integer | |

Returns the number of grouped source objects. | COUNT() 1 | — | |||

Returns the total number of qualified source objects. | COUNT() 2 | ||||

COLLECT | Gathers numeric values from grouped source objects and combines the values into lists. | COLLECT(op1) 1 | Numeric | List with element type the same as op1 | |

Gathers numeric values from all qualified source objects and combines the values into a list. | COLLECT(op1) 2 | ||||

AVG | Returns the average of all element values of a numeric collection or sequence. | AVG(op1) | Collection or sequence of numeric values | Real | |

Returns the average of the numeric values obtained from grouped source objects. | AVG(op1) 1 | Numeric | |||

Returns the average of the numeric values obtained from all qualified source objects. | AVG(op1) 2 | ||||

MAX | Returns the maximum element value in a numeric collection or sequence | MAX(op1) | Collection or sequence of numeric values | Same as element type of op1 | |

Returns the largest numeric value obtained from grouped source objects. | MAX(op1) 1 | Numeric | Same as op1 | ||

Returns the largest numeric value obtained from all qualified source objects. | MAX(op1) 2 | ||||

MIN | Returns the minimum element value in a numeric collection or sequence | MIN(op1) | Collection or sequence of numeric values | Same as element type of op1 | |

Returns the smallest numeric value obtained from grouped source objects. | MIN(op1) 1 | Numeric | Same as op1 | ||

Returns the smallest numeric value obtained from all qualified source objects. | MIN(op1) 2 | ||||

SUM | Returns the sum of all element values of a numeric collection or sequence | SUM(op1) | Collection or sequence of numeric values | Same as element type of op1 | |

Returns the sum of the numeric values obtained from grouped source objects. | SUM(op1) 1 | Numeric | Same as op1 | ||

Returns the sum of the numeric values obtained from all qualified source objects. | SUM(op1) 2 | ||||

1. Must be used in the RETURN clause of statements that contain a GROUP BY Clause. 2. Must be used in the statement’s RETURN Clause. |

Quick Look

Count the number of service records in a vehicle’s collection:

`FROM Vehicle WHERE license == 'ABC1997' RETURN COUNT(servRecs);`

Find the vehicles with 4 or more service records:

`FROM Vehicle WHERE COUNT(servRecs) >= 4 RETURN license;`

Group the customers by state, and find the average number of reward points for the customers in each group:

FROM Customers GROUP BY address.state RETURN address.state, AVG(rewardPoints);

Find the minimum and maximum number of reward points among the customers in each state:

FROM Customers GROUP BY address.state RETURN address.state, MIN(rewardPoints), MAX(rewardPoints);

Group the customers by state, and, for each state, produce a list of the reward points belonging to the customers in that state:

FROM Customers GROUP BY address.state RETURN address.state, COLLECT(rewardPoints);

Find out how many customers live in each state:

FROM Customers GROUP BY address.state RETURN address.state, COUNT();

Return the total number of customers in the dataset:

`FROM Customers RETURN COUNT();`

Return the total number of customers livng in California:

`FROM Customers WHERE address.state == 'CA' RETURN COUNT();`

Discussion

For general information about operator formats, operands, and precedence, see About Operator Expressions.

Aggregate operators return individual values by combining, or aggregating, multiple values. You can use aggregate operators for:

An aggregate operator can perform aggregation over values in different scopes, depending on the operand and/or usage context.

Scope | Values Aggregated by Operator |

Collection or sequence | Elements of a specified persistently stored collection. |

Values in a sequence of results returned by some expression. | |

Group | Values obtained from the qualified source objects within each group produced by a GROUP BY Clause. The operator returns one aggregated value per group. |

Result set | Values obtained from all of the qualified source objects identified by the overall DO statement. The operator returns one aggregated value per statement. |

Note:Aggregate operators that combine values from objects within groups or from the query’s overall result set are also considered part of a RETURN Clause, as described in Returning an Aggregate Value.

Counting Values

The COUNT operator enables you to count values of any type.

Collection or Sequence

When the operand is an expression that evaluates to a persistently stored collection, the COUNT operator returns the number of elements in that collection. The operand is typically an attribute expression. (This usage of COUNT is also described with the Collection Operators.)

Example. The following statement returns the number of strings that are elements of the collection stored in the servRecs attribute of a particular Vehicle:

`FROM Vehicle WHERE license == 'ABC1997' RETURN COUNT(servRecs);`

When the operand is an expression that evaluates to a sequence of values, the COUNT operator returns the number of values in the sequence.

Example. The following statement uses a predicate subscript expression to test the referenced Location objects in a particular RentalCompany’s locations list. The COUNT operator then counts the number of qualifying Location references in the resulting sequence. The effect is to find out how many branches of the RentalCompany are located in California:

`FROM RentalCompany WHERE name == 'Acme Auto' RETURN COUNT(locations[address.state == 'CA']);`

Groups

When a statement includes a GROUP BY Clause to subdivide the source objects into groups, you can include the COUNT operator in the RETURN Clause to return the number of objects in each group. This usage of COUNT has no operand.

Example. The following statement uses GROUP BY to subdivide the objects of class Customer into groups according to the state they live in. The COUNT operator returns the number of Customer objects in each group. The effect is to find out how many customers live in each state.

FROM Customers GROUP BY address.state RETURN address.state, COUNT();

Overall Result Set

In a statement without a GROUP BY Clause, you can use the COUNT operator in the RETURN Clause to return the total number of objects that would otherwise be returned by the statement. This usage of COUNT has no operand.

Example. The following statement return the total number of Customer objects that satisfy the predicate in the WHERE clause. The effect is to find out how many customers live in California:

`FROM Customers WHERE address.state == 'CA' RETURN COUNT();`

If the statement did not include a WHERE clause, the COUNT operator would return the total number of Customer objects in the dataset.

Collecting Numeric Values Into a List

The COLLECT operator produces lists of numeric values gathered from multiple objects.

When a statement includes a GROUP BY Clause to subdivide the source objects into groups, you can use the COLLECT operator in the RETURN Clause to produce a list of numeric values for each group, where the values listed for a group are obtained from the group’s objects. The operand typically designates a numeric attribute of the grouped objects. The operator returns one list of values per group.

Example. The following statement uses GROUP BY to subdivide the objects of class Customer into groups according to the state they live in. For each group, the COLLECT operator gathers the rewardPoints values from every Customer object in the group and combines the gathered values into a single list. The effect is to display the reward points earned by the customers living in each state.

FROM Customers GROUP BY address.state RETURN address.state, COLLECT(rewardPoints);

Calculating Averages, Sums, Maximums, and Minimums

The numeric aggregate operators enable you to calculate a value from multiple numeric values. The numeric aggregate operators are AVG, SUM, MAX, and MIN, which return the average (arithmetic mean) of the values, the sum of the values, the greatest value, or the least value, respectively.

Collection or Sequence

When the operand is an expression that evaluates to a persistently stored collection of numeric values, a numeric aggregate operator operator performs its calculation over the elements in that collection. The operand is typically an attribute expression.

Example. The following statement returns the sum of the numeric elements of the collection stored in the listOfNumbers attribute of a Test object:

`FROM Test RETURN SUM(listOfNumbers);`

When the operand is an expression that evaluates to a sequence of numeric values, a numeric aggregate operator performs its calculation over the values in the sequence.

Example. The following statement uses a dot operator expression to obtain a sequence of numeric values, one from each referenced Location object in a particular RentalCompany’s locations list. The AVG operator then calculates the average of the values in the resulting sequence. The effect is to find out the average rate factor across all branch locations of the RentalCompany:

`FROM RentalCompany WHERE name == 'Acme Auto' RETURN AVG(locations.rateFactor);`

Groups

When a statement includes a GROUP BY Clause to subdivide the source objects into groups, you can use a numeric aggregate operator in the RETURN Clause to perform the calculation over numeric values obtained from the objects within each group. The operand typically designates a numeric attribute of the grouped objects. The operator returns one aggregated value per group.

Example. The following statement uses GROUP BY to subdivide the objects of class Customer into groups according to the state they live in. For each group, the MIN operator gathers the rewardPoints values from every object in that group and returns the lowest value. Similarly, the MAX operator returns the highest rewardPoints value within each group. The effect is to find the range of reward points earned by customers living in each state.

FROM Customers GROUP BY address.state RETURN address.state, MIN(rewardPoints), MAX(rewardPoints);