group_by¶

You can use group_by clauses with the following queries:

Select

It is used in combination with the aggregate functions - for example, Count.

Count¶

In the following query, we get a count of the number of bands per manager:

>>> from piccolo.query.functions.aggregate import Count

>>> await Band.select(
...     Band.manager.name.as_alias('manager_name'),
...     Count(alias='band_count')
... ).group_by(
...     Band.manager.name
... )

[
    {"manager_name": "Graydon", "band_count": 1},
    {"manager_name": "Guido", "band_count": 1}
]

Other aggregate functions¶

These work the same as Count. See aggregate functions.

Advanced¶

For more complex cases, use QueryString to group by a raw SQL expression or an alias created in a select query. For example:

from piccolo.query.functions.aggregate import Count
from piccolo.querystring import QueryString

await Band.select(
    QueryString("DATE(created_at) AS created_date"),
    Count(),
).group_by(
    QueryString("created_date")
)

As with other raw query helpers, only use trusted SQL strings.