Declarative Objectivity (DO) Language : Operator Expressions : Aggregate Operators
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:
Counting Values
Calculating Averages, Sums, Maximums, and Minimums
Collecting Numeric Values Into a List
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);