OQL Aggregate Functions

The aggregate functions MIN, MAX, AVG, AVG over a DISTINCT expression, SUM, SUM over a DISTINCT expression, COUNT, and COUNT over a DISTINCT expression are supported. The GROUP BY extension is also supported, and required whenever an aggregate function is used within a query with other selected fields. If there are no other aggregate functions within the query, then all fields included within a GROUP BY clause must also be part of the original projection list, and all fields included within the projection list must also be part of the GROUP BY clause.

The MIN function returns the smallest of the selected expression. The type of the expression must evaluate to a java.lang.Comparable.

The MAX function returns the largest of the selected expression. The type of the expression must evaluate to a java.lang.Comparable.

The AVG function returns the arithmetic mean of the set formed by the selected expression. The type of the expression must evaluate to a java.lang.Number. For partitioned regions, each node’s buckets provide both a sum and the number of elements to the node executing the query, such that a correct average may be computed.

The AVG function where the DISTINCT modifier is applied to the expression returns the arithmetic mean of the set of unique (distinct) values. The type of the expression must evaluate to a java.lang.Number. For partitioned regions, the distinct values in a node’s buckets are returned to the node executing the query. The query node can then calculate the avarage over the values that are unique across nodes, after eliminating duplicate values that come from separate nodes.

The SUM function returns the sum over the set formed by the selected expression. The type of the expression must evaluate to a java.lang.Number. For partitioned regions, each node’s buckets compute a sum over that node, returning that sum to the node executing the query, when then sums across all nodes.

The SUM function where the DISTINCT modifier is applied to the expression returns the sum over the set of unique (distinct) values. The type of the expression must evaluate to a java.lang.Number. For partitioned regions, the distinct values in a node’s buckets are returned to the node executing the query. The query node can then calculate the sum over the values that are unique across nodes, after eliminating duplicate values that come from separate nodes.

The COUNT function returns the quantity of values in the set formed by the selected expression. For example, to return the quantity of employees who have a positive sales amount:

SELECT count(e.sales) FROM /employees e WHERE e.sales > 0.0

The COUNT function where the DISTINCT modifier is applied returns the quantity of unique (distinct) values in the set formed by the selected expression.

GROUP BY Extension for Aggregate Functions

GROUP BY is required when aggregate functions are used in combination with other selected items. It permits ordering. For example,

SELECT ID, MAX(e.sales) FROM /employees e GROUP BY ID