JPQL - EJB 3 in Action - page 435

Using aggregations

JPQL provides these aggregate functions: AVG , COUNT , MAX , MIN , and SUM , as shown in
table 11.8 . Each function's name suggests its purpose. The aggregate functions are com-
monly used in creating report queries. You can only use a persistence field with the AVG ,
MAX , MIN , and SUM functions, but you can use any type of path expression or identifier
with the COUNT function.

Table 11.8. Aggregate functions supported by JPQL

Aggregate functions Description Return type

Returns the average value of all values
of the field it's applied to

AVG Double

Returns the number of results returned
by the query

COUNT Long

Returns the maximum value of the field
it's applied to

Depends on the type of the persistence
field

MAX

Returns the minimum value of the field
it's applied to

Depends on the type of the persistence
field

MIN

Returns the sum of all values on the
field it's applied to

SUM May return either Long or Double

If you want to find the MAX value for the i.itemPrice field among all items, use the
following query:

SELECT MAX(i.itemPrice)
FROM Item i

If you want to find out how many Category entities exist in the system, use COUNT like
this:

SELECT COUNT(c)
FROM Category c

You've just seen some simple examples of aggregate functions. In the next section you'll
learn how to aggregate results based on a path expression.