The Java EE 6 Tutorial, Volume I

#### Aggregate Functions in the SELECT Clause

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

Table 21–11 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’```