7.10. 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
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.