14.12. The group by clause
A query that returns aggregate values may be grouped by any property of a returned class or components:
select cat.color, sum(cat.weight), count(cat)
from Cat cat
group by cat.color
select foo.id, avg(name), max(name)
from Foo foo join foo.names name
group by foo.id
A having clause is also allowed.
select cat.color, sum(cat.weight), count(cat)
from Cat cat
group by cat.color
having cat.color in (eg.Color.TABBY, eg.Color.BLACK)
SQL functions and aggregate functions are allowed in the having and order by clauses, if supported by the underlying database (eg. not in MySQL).
select cat
from Cat cat
join cat.kittens kitten
group by cat.id, cat.name, cat.other, cat.properties
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc
Note that neither the group by clause nor the order by clause may contain arithmetic expressions. Also note that Hibernate currently does not expand a grouped entity, so you can't write group by cat if all properties of cat are non-aggregated. You have to list all non-aggregated properties explicitly.
