Aggregate Functions in the SELECT Clause (The Java EE 5 Tutorial)

The Java EE 5 Tutorial

The result of a query may be the result of an aggregate function, listed in Table 27–10.

Table 27–10 Aggregate Functions in Select Statements


Name	Return Type	Description
`AVG`	`Double`	Returns the mean average of the fields.
`COUNT`	`Long`	Returns the total number of results.
`MAX`	the type of the field	Returns the highest value in the result set.
`MIN`	the type of the field	Returns the lowest value in the result set.
`SUM`	`Long` (for integral fields)`Double` (for floating point fields)`BigInteger` (for `BigInteger` fields)`BigDecimal` (for `BigDecimal` fields)	Returns the sum of all the values in the result set.

For select method queries with an aggregate function (AVG, COUNT, MAX, MIN, or SUM) in the SELECT clause, the following rules apply:

For the AVG, MAX, MIN, and SUM functions, the functions return null if there are no values to which the function can be applied.
For the COUNT function, if there are no values to which the function can be applied, COUNT returns 0.

The following example returns the average order quantity:

SELECT AVG(o.quantity)
 FROM Order o

The following example returns the total cost of the items ordered by Roxane Coss:

SELECT SUM(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

The following example returns the total number of orders:

SELECT COUNT(o)
FROM Order o

The following example returns the total number of items in Hal Incandenza’s order that have prices:

SELECT COUNT(l.price)
FROM Order o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = ’Incandenza’ AND c.firstname = ’Hal’