Aggregation and Grouping

Aggregation is the analytical side of the query API: COUNT, SUM, AVG, MIN, MAX over a set of rows, and GROUP BY for the per-category version. In Peewee the functions are the fn namespace and the convenience Count/Sum/Avg/Min/Max objects, and the shape of every aggregate query is the same - select the function (and the grouping columns), group by, optionally have, and execute for a scalar or a list of rows. This chapter covers the scalar aggregates, the grouped form, the count() shortcut, and the patterns (per-author stats, daily totals) that are the app's reporting queries.

Scalar Aggregates: One Number

from peewee import *

# the convenience objects - the common cases
total = Article.select().count()                    # COUNT(*)
n_pub = Article.select().where(Article.published == True).count()

# the fn namespace - any SQL function, by name
sum_views = Article.select(fn.SUM(Article.views)).scalar()
avg_rating = Article.select(fn.AVG(Article.rating)).scalar()
loves = Article.select(fn.MIN(Article.views)).scalar()
hi = Article.select(fn.MAX(Article.views)).scalar()

# .scalar(): run the query and return the single value
# (a COUNT/SUM over a query is a one-row, one-column result)
print(total, sum_views, avg_rating, loves, hi)

The two access styles cover all scalar aggregates. The convenience objects (count() on a query, fn.SUM(field) selected on a query) build the function call, and .scalar() is the execution that returns the single value rather than a row object - the right call when the answer is one number. count() on the query is the standing shortcut for COUNT(*) over the filtered set; the fn form generalizes to any function (fn.SUM, fn.AVG, the database-specific ones). The filter before the aggregate is the ordinary where - Article.select(fn.SUM(Article.views)).where(...) sums only the matching rows, which is how "total views of published articles" is one statement. The scalar() result for an empty set is the SQL convention (SUM of nothing is NULL, COUNT is 0), which is the None-handling to expect on an empty filter.

Grouped: Per-Category Statistics

from peewee import *

# per-author stats: group by author, count and sum within each group
stats = (Author.select(
                 Author.name,
                 fn.COUNT(Article.id).alias("post_count"),
                 fn.SUM(Article.views).alias("total_views"),
             )
             .join(Article, JOIN.LEFT)
             .group_by(Author.id, Author.name)
             .order_by(fn.SUM(Article.views).desc())
             .execute())

for s in stats:
    print(s.name, s.post_count, s.total_views)

# the group_by columns and the selected non-aggregated columns must match -
# every column in select that is not an aggregate must be in group_by

The grouped query selects the grouping columns (the category - here the author's name) alongside the aggregate functions, and .group_by(...) states the grouping. The SQL rule that the ORM passes through: every selected column that is not an aggregate must appear in group_by, which is why Author.name is in both (some backends are strict about it, and matching them is the portable form). The alias on the aggregate gives the result column a readable name (post_count, total_views) that the row object exposes as an attribute. The JOIN.LEFT is deliberate - an author with zero articles still appears (with a 0 count and a NULL sum), which an inner join would drop. The order_by on the aggregate (fn.SUM(Article.views).desc()) ranks the groups by their total, the standard "top authors by views" report.

HAVING: Filtering the Groups

from peewee import *

# where() filters ROWS before the group; having() filters GROUPS after
# authors with MORE THAN 5 published articles:
active = (Author.select(
                  Author.name,
                  fn.COUNT(Article.id).alias("n"),
              )
              .join(Article, on=(Article.author == Author &
                                 Article.published == True))
              .group_by(Author.id)
              .having(fn.COUNT(Article.id) > 5)
              .execute())

# the distinction, precisely:
#   WHERE  - applied to rows, before grouping (cannot reference aggregates)
#   HAVING - applied to groups, after (can reference aggregates)
# "published" is a row property - it goes in the join/where.
# "more than 5" is a group property - it goes in having.

The having() clause is the aggregate filter - the condition on the grouped result rather than the rows. The distinction is the SQL one, and it is the conceptual core of the chapter: where decides which rows enter the groups, having decides which groups survive. A condition that names an aggregate (COUNT(...) > 5) can only live in having; a condition on a row field (published == True) belongs in the where or the join condition. The two compose - filter the rows first (only published articles), group, then filter the groups (only authors above the threshold) - and getting the split right is the difference between a report that is correct and one that is subtly off. The join(on=...) form carries the row filter into the join so the count reflects only the matching rows.

The Reporting Patterns

from peewee import *

# daily view totals: group by the date part of a timestamp
# (the date function is dialect-specific; the Sql() form is the escape)
daily = (Article.select(
                fn.DATE(Article.created_at).alias("day"),
                fn.COUNT(Article.id).alias("posts"),
                fn.SUM(Article.views).alias("views"),
            )
            .group_by("day")
            .order_by("day")
            .execute())

# top articles: rank by an aggregate, take the head
top = (Article.select(
                Article.title,
                Article.views,
            )
            .where(Article.published == True)
            .order_by(Article.views.desc())
            .limit(10)
            .execute())

# distinct counts: the number of UNIQUE values
distinct_authors = Article.select(
    fn.COUNT(fn.DISTINCT(Article.author))).scalar()

The reporting queries are the grouped form with the domain's grouping key: fn.DATE(created_at) for daily buckets (the date-extraction function is dialect-specific - SQLite's DATE, PostgreSQL's created_at::date - which is where the raw Sql() object enters when the fn name does not match the backend), a rank-and-limit for the "top N," and fn.COUNT(fn.DISTINCT(field)) for the unique-value count. The Sql() raw expression is the standing escape hatch for the function or fragment that the fn namespace does not spell portably - it is the raw SQL, parameterized where it takes a value, and it composes with the rest of the expression. The pattern that unifies them: the report is a select of the grouping columns plus the aggregates, a group_by, the having when the groups are filtered, and the order_by/limit for the presentation - the same four-step shape as the read query, pointed at aggregates instead of rows.

Summary

Scalar aggregates use the convenience objects (query.count(), fn.SUM/AVG/MIN/MAX(field)) with .scalar() to return the single value, filtered by the ordinary where. Grouped statistics select the grouping columns plus the aliased aggregates, state the group_by (matching every non-aggregate selected column), and use JOIN.LEFT so empty categories still appear; having() filters the groups after aggregation, while where filters the rows before - the split is the conceptual core. The reporting patterns - daily buckets, top-N by an aggregate, distinct counts - are the same four-step shape with the domain's grouping key, and the raw Sql() expression is the escape for the dialect-specific function the fn namespace does not cover. Aggregation in Peewee is the read query pointed at the functions, and the generated SQL is the check that the grouping and the filter are where they should be.