group_by¶

You can use group_by clauses with the following queries:

Select

It is used in combination with aggregate functions - Count is currently supported.

Count¶

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

>>> from piccolo.query.methods.select import Count

>>> await Band.select(
...     Band.manager.name,
...     Count(Band.manager)
... ).group_by(
...     Band.manager
... )

[
    {"manager.name": "Graydon", "count": 1},
    {"manager.name": "Guido", "count": 1}
]

Source¶

class piccolo.query.methods.select.Count(column: Optional[Column] = None, alias: str = 'count')¶

Used in conjunction with the group_by clause in Select queries.

If a column is specified, the count is for non-null values in that column. If no column is specified, the count is for all rows, whether they have null values or not.

await Band.select(Band.name, Count()).group_by(Band.name)

# We can use an alias. These two are equivalent:

await Band.select(
    Band.name, Count(alias="total")
).group_by(Band.name)

await Band.select(
    Band.name,
    Count().as_alias("total")
).group_by(Band.name)