group_by¶
You can use group_by
clauses with the following queries:
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 inSelect
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)