When you need stats like “the count of X per Y” — revenue per category, for example — do not load every record and count with a Python loop. Just put values() before annotate(): Django translates it into a single SQL statement with GROUP BY on the values() columns and returns one dict per group. This works for every grouped report and dashboard.

The problem

Suppose an order model links to a category and you need a revenue report per category. The “natural” but poor approach loads all orders and groups them in Python:

orders = Order.objects.select_related("category").all()
report = {}
for order in orders:
    name = order.category.name
    report[name] = report.get(name, 0) + order.amount

With 100,000 orders, this code pulls 100,000 records into memory just to add up a few numbers. If you forget select_related, each access to order.category.name fires another query — the classic N+1 shape. Grouping and summing in SQL is a job the database does far better than Python.

The solution

Put values() before annotate(): the columns in values() are the grouping keys, and annotate() computes over each group:

from django.db.models import Count, Sum
report = (
    Order.objects.values("category__name")
    .annotate(total=Sum("amount"), num_orders=Count("id"))
    .order_by("-total")
)
for row in report:
    print(row["category__name"], row["num_orders"], row["total"])

Django generates exactly one SQL statement:

SELECT category.name, SUM(order.amount), COUNT(order.id)
FROM order JOIN category ON order.category_id = category.id
GROUP BY category.name
ORDER BY SUM(order.amount) DESC

Three things to remember to use it correctly. First, the result is a dict, not a model instance, so you cannot call .save() on it — fetch any column you need right inside values(). Second, the position of filter() changes its meaning: before annotate() it is WHERE (filtering input rows), after annotate() it is HAVING (filtering on a computed column, for example only groups with revenue above 1 million). Third, every column in order_by() gets pulled into GROUP BY — only sort by a grouped column or an annotated column (like -total), and call .order_by() with no arguments to clear a stray ordering.

Apply it to your project

Next time you build a dashboard or a “per …” report, ask yourself: am I loading a whole table and grouping it in Python? If so, switch to values() + annotate() right away — a single query. Another good habit: check the real SQL with print(queryset.query) before going to production — one stray order_by() is enough to silently split your groups.

Related posts

Frequently asked questions

How is values() + annotate() different from plain annotate()?

Plain annotate() attaches a computed column to each record (for example each author with a post count). values() placed before annotate() changes the meaning: the queryset is grouped by the values() columns and each group becomes a single dict — the equivalent of GROUP BY in SQL.

What does it return — can I use it like a model?

No. values() turns the queryset into a list of dicts, each containing the values() columns plus the annotated columns. You cannot call model methods or .save() on a dict.

Where do I put filter() to filter on a computed column?

Put filter() after annotate() — for example .annotate(total=Sum("amount")).filter(total__gt=1000000) — Django translates it into HAVING, filtering on grouped results. Placed before annotate(), it only filters input rows, the equivalent of WHERE.

Why did my groups split unexpectedly even though the code looks right?

Usually because you sorted by a column outside the group, for example .order_by("status"): Django adds that column to GROUP BY, so one group splits per status. Only sort by a grouped column or an annotated column, or call .order_by() with no arguments and then sort again with .order_by("-total").

💬 Have you tried this in your own project? If you hit an error or know a cleaner way, leave a comment below — I read them all and will fold the best ideas into the post.

Sources