7.10. The group by clause

A query that returns aggregate values may be grouped by any property of a returned class or components:

Copy to Clipboard Toggle word wrap

select cat.color, sum(cat.weight), count(cat) 
from Cat cat
group by cat.color

select cat.color, sum(cat.weight), count(cat) 
from Cat cat
group by cat.color

Copy to Clipboard Toggle word wrap

select foo.id, avg(name), max(name) 
from Foo foo join foo.names name
group by foo.id

select foo.id, avg(name), max(name) 
from Foo foo join foo.names name
group by foo.id

A having clause is also allowed.

Copy to Clipboard Toggle word wrap

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)

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

Copy to Clipboard Toggle word wrap

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

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.