Aggregate functions

Avg

class piccolo.query.functions.aggregate.Avg(identifier: Column | QueryString | str, alias: str | None = None)

AVG() SQL function. Column type must be numeric to run the query.

await Band.select(Avg(Band.popularity))

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

await Band.select(
    Avg(Band.popularity, alias="popularity_avg")
)

await Band.select(
    Avg(Band.popularity).as_alias("popularity_avg")
)

Count

class piccolo.query.functions.aggregate.Count(column: Column | None = None, distinct: Sequence[Column] | None = None, alias: str = 'count')

Used in Select queries, usually in conjunction with the group_by clause:

>>> await Band.select(
...     Band.manager.name.as_alias('manager_name'),
...     Count(alias='band_count')
... ).group_by(Band.manager)
[{'manager_name': 'Guido', 'count': 1}, ...]

It can also be used without the group_by clause (though you may prefer to the Table.count method instead, as it’s more convenient):

>>> await Band.select(Count())
[{'count': 3}]

Min

class piccolo.query.functions.aggregate.Min(identifier: Column | QueryString | str, alias: str | None = None)

MIN() SQL function.

await Band.select(Min(Band.popularity))

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

await Band.select(
    Min(Band.popularity, alias="popularity_min")
)

await Band.select(
    Min(Band.popularity).as_alias("popularity_min")
)

Max

class piccolo.query.functions.aggregate.Max(identifier: Column | QueryString | str, alias: str | None = None)

MAX() SQL function.

await Band.select(
    Max(Band.popularity)
)

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

await Band.select(
    Max(Band.popularity, alias="popularity_max")
)

await Band.select(
    Max(Band.popularity).as_alias("popularity_max")
)

Sum

class piccolo.query.functions.aggregate.Sum(identifier: Column | QueryString | str, alias: str | None = None)

SUM() SQL function. Column type must be numeric to run the query.

await Band.select(
    Sum(Band.popularity)
)

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

await Band.select(
    Sum(Band.popularity, alias="popularity_sum")
)

await Band.select(
    Sum(Band.popularity).as_alias("popularity_sum")
)