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

Copy to Clipboard

Toggle word wrap

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)

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)

Copy to Clipboard

Toggle word wrap

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

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

Copy to Clipboard

Toggle word wrap

Note that neither the group by clause nor the order by clause may contain arithmetic expressions.

トップに戻る