background image

The GROUP BY Clause

<< The DISTINCT Keyword | Part 6. Services >>
<< The DISTINCT Keyword | Part 6. Services >>

The GROUP BY Clause

When using the ORDER BY clause, the SELECT clause must return an orderable set of objects or
values. You cannot order the values or objects for values or objects not returned by the SELECT
clause. For example, the following query is valid because the ORDER BY clause uses the objects
returned by the SELECT clause:
SELECT o
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state =
'CA'
ORDER BY o.quantity, o.totalcost
The following example is not valid because the ORDER BY clause uses a value not returned by the
SELECT
clause:
SELECT p.product_name
FROM Order o, IN(o.lineItems) l JOIN o.customer c
WHERE c.lastname =
'Faehmel' AND c.firstname = 'Robert'
ORDER BY o.quantity
The GROUP BY Clause
The GROUP BY clause allows you to group values according to a set of properties.
The following query groups the customers by their country and returns the number of
customers per country:
SELECT c.country, COUNT(c)
FROM Customer c GROUP BY c.country
The HAVING Clause
The HAVING clause is used with the GROUP BY clause to further restrict the returned result of a
query.
The following query groups orders by the status of their customer and returns the customer
status plus the average totalPrice for all orders where the corresponding customers has the
same status. In addition, it considers only customers with status 1, 2, or 3, so orders of other
customers are not taken into account:
SELECT c.status, AVG(o.totalPrice)
FROM Order o JOIN o.customer c
GROUP BY c.status HAVING c.status IN (1, 2, 3)
Full Query Language Syntax
The Java EE 5 Tutorial · September 2007
762