Aggregation, Expressions & Functions

Each argument is an aggregate expression. The dictionary keys are generated from the field and the function (price__avg), or you choose them with keyword arguments.

Source: Generating aggregates over a QuerySet.

Unnamed arguments appear in the result under their generated name; keyword arguments are listed first, which is why total precedes price__min above. Aggregating an empty queryset returns None for Sum, Avg, Min and Max and 0 for Count. Pass default= to return something else; Django wraps the aggregate in Coalesce for you.

Source: QuerySet.aggregate() and the default argument. The default argument has existed since Django 4.0; before that, books and old code wrap the aggregate in Coalesce by hand.

The asynchronous twin is aaggregate(), which is awaited and returns the same dictionary; it is awaited from async code (an async view or an async test) and ran in the test shown later. In a synchronous view nothing changes.

Source: QuerySet.aggregate() and Asynchronous support.

All of these live in django.db.models since Django 6.0 or 6.1; before that, some of them were only available in django.contrib.postgres.

Source: Aggregation functions. The 6.0 and 6.1 release notes list StringAgg, AnyValue, the order_by argument and the BitAnd/BitOr/BitXor move: 6.0 and 6.1.

Every aggregate accepts a filter= argument, a Q object that becomes a SQL FILTER (WHERE …​) clause (or an equivalent CASE on databases that lack FILTER). It lets one query compute several differently-restricted numbers, which is far cheaper than several queries.

Source: Aggregate filter argument. The filter argument was added in Django 2.0.

annotate() follows relationships, forwards and backwards, and groups by the primary key (and the other selected columns) of the model being queried.

Source: Generating aggregates for each item in a QuerySet.

The annotation can then be used like any field: filter on it (becoming HAVING), order by it, or aggregate over it.

Source: Filtering on annotations and Aggregating annotations.

The same ORM expression runs in one query whatever the table size; Author.objects.annotate(n=Count("books")) costs one round trip where a loop with author.books.count() costs one per author (the N+1 problem of Query Optimization & Fetch Modes).

To group by something other than the whole object, call values() first. The fields named in values() become the GROUP BY columns and the following annotate() computes one row per group. The result is a queryset of dictionaries.

Source: Order of annotate() and values() clauses.

The order matters. annotate().values() groups by the object and then trims the columns, whereas values().annotate() groups by the named columns. Also remember that a model’s Meta.ordering (or a previous order_by()) adds columns to the GROUP BY and splits groups; call .order_by() with the grouping fields (or no arguments) when a grouped query returns more rows than expected. Details are on Interaction with order_by().

When the aggregate and the filter touch the same multi-valued relationship, the order of the calls changes the result. A filter() placed before annotate() restricts the rows that are aggregated; placed after, a filter on the relationship adds a second join that multiplies the rows being counted.

Source: Order of annotate() and filter() clauses.

The same multiplication bites when you aggregate over two different relationships in one query. Each extra join repeats the rows of the other, so every Count over the multiplied relationship needs distinct=True; Sum and Avg over a multiplied relationship cannot be fixed that way and need subqueries instead (see below).

Source: Combining multiple aggregations.

The test below locks the behaviour in, and also shows how to assert that an annotated queryset is a single query.

Source: assertNumQueries.

alias() (Django 3.2) takes the same arguments as annotate() but does not add the value to the SELECT list. Use it for intermediate values you only filter or order by, or that you reuse in a later expression, so the database does not return a column you ignore.

Source: QuerySet.alias() and the 3.2 release notes.

F("name") is a reference to a model field (or an annotation), evaluated by the database. It supports +, -, , /, %, * and, on bit-capable databases, bitwise operators. It lets you compare two columns, and update a row without a read-modify-write cycle that would race with other requests.

Source: F() expressions and Making queries.

Two points are worth knowing. First, after save() with an F() assigned, the attribute still holds the expression on older versions, so a second save() would apply it again. Since Django 6.0 the field is refreshed from the database after save() on SQLite, PostgreSQL and Oracle (the backends with a RETURNING clause) and marked deferred on MySQL and MariaDB; on 5.2 LTS call refresh_from_db() before reusing the instance, as above. Second, F() on a character or array field can be sliced since Django 5.1.

Value(x) wraps a Python constant so it can take part in an expression. When an expression mixes field types, Django cannot always infer the result type and raises FieldError: Expression contains mixed types; the cure is to set output_field on the expression (or wrap it in ExpressionWrapper).

Source: Value(), ExpressionWrapper() and Using F() with annotations.

The Cast("pages", FloatField()) matters on SQLite: with two integer-looking operands SQLite performs integer division, so a DecimalField price stored as 10 divided by 300 would give 0. PostgreSQL does numeric division and would not need the cast, which is a good reason to run the test suite against the database you deploy on (see PostgreSQL & Database Backends).

Two features remove the old "annotate, then filter on the annotation" dance. Since Django 3.0 an expression that outputs a boolean (an Exists(), a Q wrapped in ExpressionWrapper) can be used directly in filter(). Since 4.0, lookups are expressions too: GreaterThan(F("a"), F("b")) from django.db.models.lookups works in annotations, aggregations and filters.

Source: Using F() with logical operations, Built-in expressions and the 3.0 and 4.0 release notes.

Func is the base class of the SQL function wrappers. Used directly it calls any function by name; subclassed it makes a reusable one. The class attributes are function (the SQL name), template (the SQL pattern), arg_joiner (the separator between arguments) and arity (the exact number of arguments, enforced with a TypeError).

Source: Func() expressions.

Never build a Func template with string formatting of untrusted input: arguments should always be passed as expressions or Value(), so the driver parameterises them. The reference page Avoiding SQL injection describes the one trap, which is passing user-controlled **extra keys to Func or an aggregate.

An aggregate is a Func that is allowed to appear in a GROUP BY context. Subclass Aggregate, set function, and declare capabilities with the class attributes allow_distinct, allow_order_by (6.0) and empty_result_set_value.

Source: Aggregate expressions and Creating your own aggregate functions. Prefer the built-in StringAgg (6.0) over a custom backend-specific one wherever possible.

A plain Func subclass used where an aggregate is required fails with TypeError: …​ is not an aggregate expression; the aggregate machinery is what makes aggregate() accept it.

Case in update() changes different rows differently in one statement.

Source: Conditional update.

Case/When inside Sum/Count is the classic way to count by category in a single query. The shorter filter= form is equivalent and clearer; both compile to a single query (Django emits a CASE expression on databases without the FILTER clause).

Source: Conditional aggregation.

Case is a normal expression, so it can drive order_by(), for example to put hardcovers first; and a Q(…​) | Exists(…​) condition mixes a column test with a correlated test in one WHERE.

Source: Conditional filter.

Exists(queryset) is a Subquery that returns a boolean, and it is the efficient way to ask "is there at least one related row?". It compiles to EXISTS (SELECT 1 …​ LIMIT 1), so the database stops at the first match instead of counting. It can be annotated, negated with ~, and, since Django 3.0, used directly in filter().

Source: Exists() subqueries.

Pitfalls: Exists is for "any row", Subquery for "this value"; using Count(…​) > 0 instead builds a join and a GROUP BY where EXISTS is a semi-join. Do not forget OuterRef, because without it the inner queryset is uncorrelated and Exists is true or false for every row at once. The older queryset.filter(relatedisnull=False).distinct() pattern also works but goes through a join and needs distinct(); an in lookup such as Author.objects.filter(pkin=Book.objects.filter(pagesgt=400).values("author")) is the right tool when you do not need correlation (['Ann', 'Cy'] in the seed data).

RowNumber numbers rows 1, 2, 3, …​ with no ties. Rank gives tied rows the same number and leaves a gap afterwards; DenseRank leaves no gap. Ntile(n) splits the ordered rows into n buckets. Always give an order_by to the ranking functions; the numbering is otherwise arbitrary.

Source: Rank, DenseRank, RowNumber and Ntile.

A frame restricts the rows a window aggregate sees relative to the current row. RowRange(start, end) counts physical rows; ValueRange(start, end) uses the values of the ordering column. None means unbounded and 0 means the current row. A running total is "from the start to the current row"; a moving average of three rows is "one before to one after".

Source: Window frames, RowRange and ValueRange. Since 5.1, RowRange accepts positive start and negative end values and both frames take an exclusion argument (a WindowFrameExclusion member) to exclude the current row, its group or its ties.

SQL does not allow WHERE on a window result, so "the top two books of each author" used to require a subquery or Python. Since Django 4.2 you can filter on a window annotation directly; Django generates the wrapping query. The restriction is that the lookups must not be disjunctive (no OR/XOR connecting a window condition) in a queryset that also aggregates.

Source: Window functions. The 4.2 release notes: Django 4.2.

Window functions need database support, which every backend Django 6.1 supports provides; a third-party backend may not. If a combination raises NotSupportedError on your database, fall back to a subquery.

Source: Comparison and conversion functions: Cast, Coalesce, Collate, Greatest, Least, NullIf.

Coalesce returns its first non-null argument and is how you turn the None of an empty aggregate into a number (before the default= argument existed, and still for non-aggregate expressions). Greatest and Least skip null arguments on PostgreSQL but return NULL if any argument is null on the other databases, as the reference notes, so wrap nullable arguments in Coalesce for portable code. Collate changes the collation for one comparison (for example NOCASE on SQLite, a custom ICU collation on PostgreSQL). The Round function takes a precision since Django 4.0.

Source: Text functions: Chr, Concat, Left, Length, Lower, LPad, LTrim, MD5, Ord, Repeat, Replace, Reverse, Right, RPad, RTrim, SHA1 to SHA512, StrIndex, Substr, Trim, Upper.

Mixing a field and a Value in Concat needs an explicit output_field only when the field types differ; here Cast makes the primary key text. StrIndex is one-based and returns 0 when the substring is not found. Lower("title") combined with a __exact lookup is the portable case-insensitive comparison; for sorting and searching in PostgreSQL, see the collation and unaccent notes in PostgreSQL & Database Backends.

Extract* returns one component (year, month, week day, quarter, hour, …​) as an integer. Trunc* rounds a date or datetime down to a unit and keeps the type, which is what you group by to build time series. Time zone handling follows USE_TZ and the current time zone; since Django 5.0 USE_TZ is True by default.

Source: Date functions, Extract, Trunc and Now.

ExtractWeekDay numbers Sunday as 1 whereas ExtractIsoWeekDay numbers Monday as 1; ExtractWeek is the ISO week (weeks start on Monday). TruncMonth over a DateTimeField returns an aware datetime in the current time zone, as shown. Now() is also usable as a db_default since 5.0 (see Models & Fields).

Source: JSON functions and Math functions. JSONObject is Django 3.2 and JSONArray is 5.2.

The math group has Abs, ACos, ASin, ATan, ATan2, Ceil, Cos, Cot, Degrees, Exp, Floor, Ln, Log, Mod, Pi, Power, Radians, Random, Round, Sign, Sin, Sqrt and Tan. Random orders unpredictably (order_by(Random())), which scans the whole table: avoid it on large tables.

Django 6.1 adds UUID4 and UUID7 as database functions, so the database itself can generate identifiers, for example as a db_default or in a bulk update. UUID7 (time-ordered, index-friendly) takes an optional shift argument that moves the embedded timestamp. Support depends on the backend:

Source: UUID functions and the 6.1 release notes. The example was run on SQLite with Python 3.13, which is why UUID7 raised; on PostgreSQL 18 it returns a time-ordered UUID. On older databases, keep generating UUIDs in Python with default=uuid.uuid4 and decide per project whether database-side generation is worth the version floor.