JPQL - EJB 3 in Action - page 436

Grouping with GROUP BY and HAVING
In an Enterprise business application, you may need to group data by a persistence field.
Assuming that there's a one-to-many relationship between User and Category , this
query will generate a report that lists the number of Category entities created by each
c.user :
SELECT c.user, COUNT(c.categoryId)
FROM Category c
GROUP BY c.user
As you can see, this code is grouped by an associated entity. You can group by a single-
value path expression that's either a persistence or an association field. Only aggregate
functions are allowed when you perform aggregation using GROUP BY . You can also filter
the results of an aggregated query with a HAVING clause. Suppose you want to retrieve
only the users who have created more than five Category entities. Simply modify the
previous query as follows:
SELECT c.user, COUNT(c.categoryId)
FROM Category c
GROUP BY c.user
HAVING COUNT(c.categoryId) > 5
In addition, you can have a WHERE clause in a query along with a GROUP BY clause such
as this:
SELECT c.user, COUNT(c.categoryId)
FROM Category c
WHERE c.createDate is BETWEEN :date1 and :date2
GROUP BY c.user
HAVING COUNT(c.categoryId) > 5
A WHERE clause in a query containing both the GROUP BY and HAVING clauses results
in multistage processing. First, the WHERE clause is applied to filter the results. Then, the
results are aggregated based on the GROUP BY clause. Finally, the HAVING clause is ap-
plied to filter the aggregated result.