SELECT Clause

The SELECT clause defines the types of the objects or values returned by the query.

Return Types

The return type of the SELECT clause is defined by the result types of the select expressions contained within it. If multiple expressions are used, the result of the query is an Object[], and the elements in the array correspond to the order of the expressions in the SELECT clause, and in type to the result types of each expression.

A SELECT clause cannot specify a collection-valued expression. For example, the SELECT clause p.teams is invalid because teams is a collection. However, the clause in the following query is valid because the t is a single element of the teams collection:

SELECT t
FROM Player p, IN (p.teams) t

The following query is an example of a query with multiple expressions in the select clause:

SELECT c.name, c.country.name
 FROM customer c
 WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’

It returns a list of Object[] elements where the first array element is a string denoting the customer name and the second array element is a string denoting the name of the customer’s country.

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’

The DISTINCT Keyword

The DISTINCT keyword eliminates duplicate return values. If a query returns a java.util.Collection, which allows duplicates, then you must specify the DISTINCT keyword to eliminate duplicates.

Constructor Expressions

Constructor expressions allow you to return Java instances that store a query result element instead of an Object[].

The following query creates a CustomerDetail instance per Customer matching the WHERE clause. A CustomerDetail stores the customer name and customer’s country name. So the query returns a List of CustomerDetail instances:

SELECT NEW com.xyz.CustomerDetail(c.name, c.country.name)
 FROM customer c
WHERE c.lastname = ’Coss’ AND c.firstname = ’Roxane’