Aggregation functions
Aggregation functions operate on a set of values to return a single scalar value. We can use these functions in the select and subselect methods.
The following example illustrates an aggregation:
SELECT SUM (e.salary) FROM EmpBean e WHERE e.dept.deptno =20
This aggregation computes the total salary for department 20.
The aggregation functions are AVG, COUNT, MAX, MIN, and SUM. The syntax of an aggregation function is illustrated in the following example:
aggregation-function ( [ ALL | DISTINCT ] expression )
or:
COUNT( [ ALL | DISTINCT ] identification-variable )
or:
COUNT( * )
The DISTINCT option eliminates duplicate values before applying the function. ALL is the default option and does not eliminate duplicates. Null values are ignored in computing the aggregate function except in the cases of COUNT(*) and COUNT(identification-variable), which return a count of all the elements in the set.
If the datastore is Informix, you must limit the expression argument to a single valued path expression when using the COUNT function or the DISTINCT forms of the functions SUM, AVG, MIN, and MAX.
Define return type
For a select method using an aggregation function, we can define the return type as a primitive type or a wrapper type. The return type must be compatible with the return type from the datastore. The MAX and MIN functions can apply to any numeric, string or datetime datatype and return the corresponding datatype. The SUM and AVG functions take a numeric type as input, and return the same numeric type used in the datastore. The COUNT function can take any datatype, and returns an integer.
When applied to an empty set, the SUM, AVG, MAX, and MIN functions can return a null value. The COUNT function returns zero (0) when it is applied to an empty set. Use wrapper types if the return value might be NULL; otherwise, the container displays an ObjectNotFound exception.
Use GROUP BY and HAVING
The set of values used for the aggregate function is determined by the collection that results from the FROM and WHERE clause of the query. We can divide the set into groups and apply the aggregation function to each group. To perform this action, use a GROUP BY clause in the query. The GROUP BY clause defines grouping members, which comprise a list of path expressions. Each path expression specifies a field that is a primitive type of byte, short, int, long, float, double, boolean, char, or a wrapper type of Byte, Short, Integer, Long, Float, Double, BigDecimal, String, Boolean, Character, java.util.Calendar, java.util.Date, java.sql.Date, java.sql.Time or java.sql.Timestamp.
Example use of the GROUP BY clause in a query that computes the average salary for each department:
SELECT e.dept.deptno, AVG ( e.salary) FROM EmpBean e GROUP BY e.dept.deptno
In division of a set into groups, a NULL value is considered equal to another NULL value.
Just as the WHERE clause filters tuples (that is, records of the return collection values) from the FROM clause, the groups can be filtered using a HAVING clause that tests group properties involving aggregate functions or grouping members:
SELECT e.dept.deptno, AVG ( e.salary) FROM EmpBean e GROUP BY e.dept.deptno HAVING COUNT(*) > 3 AND e.dept.deptno > 5This query returns the average salary for departments that have more than three employees and the department number is greater than five.
It is possible to use a HAVING clause without a GROUP BY clause, in which case the entire set is treated as a single group, to which the HAVING clause is applied.
Related concepts
EJB query language
Related tasks
Use EJB query