In Django's ORM, how does calling values() before annotate() produce a GROUP BY, and what can silently change the grouping?

middleshould knowfreq 58%

answer

order of values and annotate matters
values first means group by those fields
annotate first means per object
order_by columns join the grouping

basics

~20 s

values("field") placed before annotate() makes Django GROUP BY those fields, giving one dict per group with the aggregate added. Putting values() after annotate() keeps per-object grouping, and extra order_by() fields silently join the GROUP BY.

solid answer

~40 s

In Django, the position of values() decides the grouping. Order.objects.values("customer__region__name").annotate(revenue=Sum("amount")) groups by region name and returns one dict per region with revenue included automatically. Reverse it, annotate(...).values(...), and the aggregate is computed per Order first; values() then only trims the output columns, and you must list the annotation explicitly. Group by a computed value by annotating it first, e.g. annotate(month=TruncMonth("placed_at")).values("month"). The classic silent breakage is ordering: any field named in order_by() is added to the GROUP BY, so ordering by a column you did not group on splits the groups; clear it with an empty order_by() or order only by grouped fields. Since Django 3.1 a model's Meta.ordering is no longer applied to these queries.

In Django, the position of `values()` decides the grouping. `Order.objects.values("customer__region__name").annotate(revenue=Sum("amount"))` groups by region name and returns one dict per region with `revenue` included automatically. Reverse it, `annotate(...).values(...)`, and the aggregate is computed per `Order` first; `values()` then only trims the output columns, and you must list the annotation explicitly. Group by a computed value by annotating it first, e.g. `annotate(month=TruncMonth("placed_at")).values("month")`. The classic silent breakage is ordering: any field named in `order_by()` is added to the `GROUP BY`, so ordering by a column you did not group on splits the groups; clear it with an empty `order_by()` or order only by grouped fields. Since Django 3.1 a model's `Meta.ordering` is no longer applied to these queries.

code

python · 16 lines

python

from django.db.models import Sum
from django.db.models.functions import TruncMonth

paid = Order.objects.filter(status="paid").order_by("placed_at")

# Wrong: placed_at from order_by() joins the GROUP BY -> one row per order.
broken = paid.values("customer__region__name").annotate(revenue=Sum("amount"))

# Right: clear the ordering, then order by grouped keys only.
monthly = (
    paid.order_by()
    .annotate(month=TruncMonth("placed_at"))
    .values("customer__region__name", "month")
    .annotate(revenue=Sum("amount"))
    .order_by("customer__region__name", "month")
)

go deeper

for a junior

Recall that values("field") before annotate() gives one dict per distinct value of that field, with the aggregate added.

for a middle

Explain why the order of values() and annotate() changes the SQL, and how order_by() fields end up in GROUP BY.

for a senior

Diagnose a report that returns one row per order instead of per region by reading str(qs.query), and fix leaking ordering or multi-valued joins.

for a principal

Set conventions for reporting queries: canonical chain order, explicit ordering, and review of generated SQL for every new dashboard query.

The rule: `values()` before `annotate()` defines the groups

In Django's ORM, annotate() normally groups by the model's primary key, so every object gets its own value. values() placed before annotate() replaces that grouping with the fields you list:

python

from django.db.models import Sum
from django.db.models.functions import TruncMonth

(
    Order.objects.filter(status="paid")
    .annotate(month=TruncMonth("placed_at"))
    .values("customer__region__name", "month")
    .annotate(revenue=Sum("amount"))
    .order_by("customer__region__name", "month")
)
# [{'customer__region__name': 'North', 'month': datetime(2026, 7, 1, ...), 'revenue': Decimal('9120.00')}, ...]

The SQL has the shape SELECT region.name, DATE_TRUNC('month', placed_at) AS month, SUM(amount) ... GROUP BY region.name, month. Things to notice:

the result is a QuerySet of dicts, not model instances;
the aggregate (revenue) is added to the dicts automatically;
to group by a computed value such as the month, annotate the non-aggregate expression first (TruncMonth from django.db.models.functions), then name it in values();
a filter() on revenue after the second annotate() becomes a HAVING clause.

The reverse order does something else

Chain	Grouping	Output
`values("region").annotate(total=Sum(...))`	by `region`	one dict per region, `total` included
`annotate(total=Sum(...)).values("region")`	by primary key	one dict per object; `total` missing unless listed
`annotate(total=Sum(...)).values("region", "total")`	by primary key	one dict per object with both keys

The documentation states it directly: when annotate() comes first, the annotation is computed over each object and values() only constrains which columns appear.

What silently changes the grouping

order_by() fields. Every field in order_by() is selected and therefore added to the GROUP BY. If Order has an unrelated ordering in the chain, say .order_by("placed_at"), then values("customer__region__name").annotate(...) groups by region and timestamp, giving one row per order instead of one per region. Fix it by ordering only by grouped fields or annotations, or by calling order_by() with no arguments to clear the ordering. Django never removes an explicit order_by() for you.
Meta.ordering. Before Django 3.1 a model's default ordering leaked into GROUP BY the same way. Since 3.1, GROUP BY queries built with values() and annotate() ignore Meta.ordering; add an explicit order_by() if you want sorted groups.
distinct() has a similar rule: ordering fields take part in SELECT DISTINCT, which is why the docs describe both together.
Multi-valued joins in values(). Grouping across a reverse foreign key or many-to-many relation also groups the joined rows, and can multiply aggregates; see the join-multiplication question.

Grouping by a related field or an expression

Related fields work with double underscores: values("customer__region__name") joins Customer and Region and groups by the region's name. Grouping by customer__region (the id) is cheaper and avoids merging two regions that share a name.
Keyword expressions can go straight into values(): values(month=TruncMonth("placed_at")) is equivalent to annotating first and then naming the alias.
Several keys simply list several fields; the group is the combination.

MySQL and non-aggregated columns

MySQL with ONLY_FULL_GROUP_BY rejects a query that selects a non-aggregated expression not listed in GROUP BY. Django 6.0 added the AnyValue aggregate for that case: wrap the column in AnyValue(...) to pick an arbitrary value per group. PostgreSQL 16+, SQLite and Oracle also support it.

A worked monthly report

For "monthly paid revenue per sales region", a typical view does the following:

Filter first: Order.objects.filter(status="paid", placed_at__year=2026) so only relevant rows are grouped.
Clear inherited ordering with order_by().
Annotate the grouping key: annotate(month=TruncMonth("placed_at")).
Group: values("customer__region__name", "month").
Aggregate: annotate(revenue=Sum("amount")).
Order the output by the grouped keys.

The result has no row for a region and month with no paid orders, because there was nothing to group. If the report needs a full grid, build the list of months in Python and fill the gaps with 0 when you pivot the dicts into a table; default=0 on Sum does not help here, since it only applies to groups that exist.

Debugging a grouping

Print str(qs.query) and read the GROUP BY clause; the docs recommend exactly this.
Count the rows: if a per-region report returns one row per order, an ordering field has leaked in.
Keep the chain in the canonical order: filter() → optional annotate() of non-aggregate keys → values() → annotate() of aggregates → order_by().

## The rule: `values()` before `annotate()` defines the groups In Django's ORM, `annotate()` normally groups by the model's primary key, so every object gets its own value. **`values()` placed before `annotate()` replaces that grouping** with the fields you list: ```python from django.db.models import Sum from django.db.models.functions import TruncMonth ( Order.objects.filter(status="paid") .annotate(month=TruncMonth("placed_at")) .values("customer__region__name", "month") .annotate(revenue=Sum("amount")) .order_by("customer__region__name", "month") ) # [{'customer__region__name': 'North', 'month': datetime(2026, 7, 1, ...), 'revenue': Decimal('9120.00')}, ...] ``` The SQL has the shape `SELECT region.name, DATE_TRUNC('month', placed_at) AS month, SUM(amount) ... GROUP BY region.name, month`. Things to notice: - the result is a QuerySet of **dicts**, not model instances; - the aggregate (`revenue`) is **added to the dicts automatically**; - to group by a **computed value** such as the month, annotate the non-aggregate expression first (`TruncMonth` from `django.db.models.functions`), then name it in `values()`; - a `filter()` on `revenue` after the second `annotate()` becomes a `HAVING` clause. ## The reverse order does something else | Chain | Grouping | Output | |---|---|---| | `values("region").annotate(total=Sum(...))` | by `region` | one dict per region, `total` included | | `annotate(total=Sum(...)).values("region")` | by primary key | one dict per object; `total` missing unless listed | | `annotate(total=Sum(...)).values("region", "total")` | by primary key | one dict per object with both keys | The documentation states it directly: when `annotate()` comes first, the annotation is computed over each object and `values()` only constrains which columns appear. ## What silently changes the grouping 1. **`order_by()` fields.** Every field in `order_by()` is selected and therefore **added to the `GROUP BY`**. If `Order` has an unrelated ordering in the chain, say `.order_by("placed_at")`, then `values("customer__region__name").annotate(...)` groups by region *and* timestamp, giving one row per order instead of one per region. Fix it by ordering only by grouped fields or annotations, or by calling **`order_by()` with no arguments** to clear the ordering. Django never removes an explicit `order_by()` for you. 2. **`Meta.ordering`.** Before Django 3.1 a model's default ordering leaked into `GROUP BY` the same way. Since 3.1, GROUP BY queries built with `values()` and `annotate()` **ignore `Meta.ordering`**; add an explicit `order_by()` if you want sorted groups. 3. **`distinct()`** has a similar rule: ordering fields take part in `SELECT DISTINCT`, which is why the docs describe both together. 4. **Multi-valued joins in `values()`.** Grouping across a reverse foreign key or many-to-many relation also groups the joined rows, and can multiply aggregates; see the join-multiplication question. ## Grouping by a related field or an expression - **Related fields** work with double underscores: `values("customer__region__name")` joins `Customer` and `Region` and groups by the region's name. Grouping by `customer__region` (the id) is cheaper and avoids merging two regions that share a name. - **Keyword expressions** can go straight into `values()`: `values(month=TruncMonth("placed_at"))` is equivalent to annotating first and then naming the alias. - **Several keys** simply list several fields; the group is the combination. ## MySQL and non-aggregated columns MySQL with `ONLY_FULL_GROUP_BY` rejects a query that selects a non-aggregated expression not listed in `GROUP BY`. Django 6.0 added the **`AnyValue`** aggregate for that case: wrap the column in `AnyValue(...)` to pick an arbitrary value per group. PostgreSQL 16+, SQLite and Oracle also support it. ## A worked monthly report For "monthly paid revenue per sales region", a typical view does the following: 1. Filter first: `Order.objects.filter(status="paid", placed_at__year=2026)` so only relevant rows are grouped. 2. Clear inherited ordering with `order_by()`. 3. Annotate the grouping key: `annotate(month=TruncMonth("placed_at"))`. 4. Group: `values("customer__region__name", "month")`. 5. Aggregate: `annotate(revenue=Sum("amount"))`. 6. Order the output by the grouped keys. The result has **no row** for a region and month with no paid orders, because there was nothing to group. If the report needs a full grid, build the list of months in Python and fill the gaps with `0` when you pivot the dicts into a table; `default=0` on `Sum` does not help here, since it only applies to groups that exist. ## Debugging a grouping - Print `str(qs.query)` and read the `GROUP BY` clause; the docs recommend exactly this. - Count the rows: if a per-region report returns one row per order, an ordering field has leaked in. - Keep the chain in the canonical order: `filter()` → optional `annotate()` of non-aggregate keys → `values()` → `annotate()` of aggregates → `order_by()`.

In Django, how do you filter the groups of a values().annotate() query, for example regions with revenue over 10,000?
Filter on the annotation after it is defined: `.values("customer__region__name").annotate(revenue=Sum("amount")).filter(revenue__gt=10000)`. Because `revenue` is an aggregate, Django puts that condition in `HAVING`, not `WHERE`. A `filter()` placed before `annotate()` restricts the rows being summed instead.
In Django, why do values().annotate() results come back unsorted after upgrading from an old release?
Since Django 3.1 the model's `Meta.ordering` is ignored in GROUP BY queries, because it used to corrupt the grouping. If the report relied on that implicit order, add an explicit `order_by()` using the grouped fields or the annotation.

saying these in an interview costs you the question

values() after annotate() groups by the listed fields
order_by() only sorts the result and never affects grouping
Meta.ordering still leaks into GROUP BY in current Django
values().annotate() returns model instances with an extra attribute
You must list the aggregate in values() when values() comes first

In Django's ORM, how does calling values() before annotate() produce a GROUP BY, and what can silently change the grouping?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The rule: values() before annotate() defines the groups

The reverse order does something else

What silently changes the grouping

Grouping by a related field or an expression

MySQL and non-aggregated columns

A worked monthly report

Debugging a grouping

▾common follow-ups2 questions

▾red flags5 flags

The rule: values() before annotate() defines the groups

The reverse order does something else

What silently changes the grouping

Grouping by a related field or an expression

MySQL and non-aggregated columns

A worked monthly report

Debugging a grouping

answer

go deeper

The rule: `values()` before `annotate()` defines the groups

The rule: `values()` before `annotate()` defines the groups