Debian Codegraph @

docs/topics/db/aggregation.txt

raw
1 ===========
2 Aggregation
3 ===========
4
5 .. currentmodule:: django.db.models
6
7 The topic guide on :doc:`Django's database-abstraction API </topics/db/queries>`
8 described the way that you can use Django queries that create,
9 retrieve, update and delete individual objects. However, sometimes you will
10 need to retrieve values that are derived by summarizing or *aggregating* a
11 collection of objects. This topic guide describes the ways that aggregate values
12 can be generated and returned using Django queries.
13
14 Throughout this guide, we'll refer to the following models. These models are
15 used to track the inventory for a series of online bookstores:
16
17 .. _queryset-model-example:
18
19 .. code-block:: python
20
21 from django.db import models
22
23
24 class Author(models.Model):
25 name = models.CharField(max_length=100)
26 age = models.IntegerField()
27
28
29 class Publisher(models.Model):
30 name = models.CharField(max_length=300)
31
32
33 class Book(models.Model):
34 name = models.CharField(max_length=300)
35 pages = models.IntegerField()
36 price = models.DecimalField(max_digits=10, decimal_places=2)
37 rating = models.FloatField()
38 authors = models.ManyToManyField(Author)
39 publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
40 pubdate = models.DateField()
41
42
43 class Store(models.Model):
44 name = models.CharField(max_length=300)
45 books = models.ManyToManyField(Book)
46
47 Cheat sheet
48 ===========
49
50 In a hurry? Here's how to do common aggregate queries, assuming the models
51 above:
52
53 .. code-block:: pycon
54
55 # Total number of books.
56 >>> Book.objects.count()
57 2452
58
59 # Total number of books with publisher=BaloneyPress
60 >>> Book.objects.filter(publisher__name="BaloneyPress").count()
61 73
62
63 # Average price across all books, provide default to be returned instead
64 # of None if no books exist.
65 >>> from django.db.models import Avg
66 >>> Book.objects.aggregate(Avg("price", default=0))
67 {'price__avg': 34.35}
68
69 # Max price across all books, provide default to be returned instead of
70 # None if no books exist.
71 >>> from django.db.models import Max
72 >>> Book.objects.aggregate(Max("price", default=0))
73 {'price__max': Decimal('81.20')}
74
75 # Difference between the highest priced book and the average price of all books.
76 >>> from django.db.models import FloatField
77 >>> Book.objects.aggregate(
78 ... price_diff=Max("price", output_field=FloatField()) - Avg("price")
79 ... )
80 {'price_diff': 46.85}
81
82 # All the following queries involve traversing the Book<->Publisher
83 # foreign key relationship backwards.
84
85 # Each publisher, each with a count of books as a "num_books" attribute.
86 >>> from django.db.models import Count
87 >>> pubs = Publisher.objects.annotate(num_books=Count("book"))
88 >>> pubs
89 <QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>
90 >>> pubs[0].num_books
91 73
92
93 # Each publisher, with a separate count of books with a rating above and below 5
94 >>> from django.db.models import Q
95 >>> above_5 = Count("book", filter=Q(book__rating__gt=5))
96 >>> below_5 = Count("book", filter=Q(book__rating__lte=5))
97 >>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
98 >>> pubs[0].above_5
99 23
100 >>> pubs[0].below_5
101 12
102
103 # The top 5 publishers, in order by number of books.
104 >>> pubs = Publisher.objects.annotate(num_books=Count("book")).order_by("-num_books")[:5]
105 >>> pubs[0].num_books
106 1323
107
108 Generating aggregates over a ``QuerySet``
109 =========================================
110
111 Django provides two ways to generate aggregates. The first way is to generate
112 summary values over an entire ``QuerySet``. For example, say you wanted to
113 calculate the average price of all books available for sale. Django's query
114 syntax provides a means for describing the set of all books:
115
116 .. code-block:: pycon
117
118 >>> Book.objects.all()
119
120 What we need is a way to calculate summary values over the objects that
121 belong to this ``QuerySet``. This is done by appending an ``aggregate()``
122 clause onto the ``QuerySet``:
123
124 .. code-block:: pycon
125
126 >>> from django.db.models import Avg
127 >>> Book.objects.all().aggregate(Avg("price"))
128 {'price__avg': 34.35}
129
130 The ``all()`` is redundant in this example, so this could be simplified to:
131
132 .. code-block:: pycon
133
134 >>> Book.objects.aggregate(Avg("price"))
135 {'price__avg': 34.35}
136
137 The argument to the ``aggregate()`` clause describes the aggregate value that
138 we want to compute - in this case, the average of the ``price`` field on the
139 ``Book`` model. A list of the aggregate functions that are available can be
140 found in the :ref:`QuerySet reference <aggregation-functions>`.
141
142 ``aggregate()`` is a terminal clause for a ``QuerySet`` that, when invoked,
143 returns a dictionary of name-value pairs. The name is an identifier for the
144 aggregate value; the value is the computed aggregate. The name is
145 automatically generated from the name of the field and the aggregate function.
146 If you want to manually specify a name for the aggregate value, you can do so
147 by providing that name when you specify the aggregate clause:
148
149 .. code-block:: pycon
150
151 >>> Book.objects.aggregate(average_price=Avg("price"))
152 {'average_price': 34.35}
153
154 If you want to generate more than one aggregate, you add another argument to
155 the ``aggregate()`` clause. So, if we also wanted to know the maximum and
156 minimum price of all books, we would issue the query:
157
158 .. code-block:: pycon
159
160 >>> from django.db.models import Avg, Max, Min
161 >>> Book.objects.aggregate(Avg("price"), Max("price"), Min("price"))
162 {'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}
163
164 Generating aggregates for each item in a ``QuerySet``
165 =====================================================
166
167 The second way to generate summary values is to generate an independent
168 summary for each object in a :class:`.QuerySet`. For example, if you are
169 retrieving a list of books, you may want to know how many authors contributed
170 to each book. Each Book has a many-to-many relationship with the Author; we
171 want to summarize this relationship for each book in the ``QuerySet``.
172
173 Per-object summaries can be generated using the
174 :meth:`~.QuerySet.annotate` clause. When an ``annotate()`` clause is
175 specified, each object in the ``QuerySet`` will be annotated with the
176 specified values.
177
178 The syntax for these annotations is identical to that used for the
179 :meth:`~.QuerySet.aggregate` clause. Each argument to ``annotate()`` describes
180 an aggregate that is to be calculated. For example, to annotate books with the
181 number of authors:
182
183 .. code-block:: pycon
184
185 # Build an annotated queryset
186 >>> from django.db.models import Count
187 >>> q = Book.objects.annotate(Count("authors"))
188 # Interrogate the first object in the queryset
189 >>> q[0]
190 <Book: The Definitive Guide to Django>
191 >>> q[0].authors__count
192 2
193 # Interrogate the second object in the queryset
194 >>> q[1]
195 <Book: Practical Django Projects>
196 >>> q[1].authors__count
197 1
198
199 As with ``aggregate()``, the name for the annotation is automatically derived
200 from the name of the aggregate function and the name of the field being
201 aggregated. You can override this default name by providing an alias when you
202 specify the annotation:
203
204 .. code-block:: pycon
205
206 >>> q = Book.objects.annotate(num_authors=Count("authors"))
207 >>> q[0].num_authors
208 2
209 >>> q[1].num_authors
210 1
211
212 Unlike ``aggregate()``, ``annotate()`` is *not* a terminal clause. The output
213 of the ``annotate()`` clause is a ``QuerySet``; this ``QuerySet`` can be
214 modified using any other ``QuerySet`` operation, including ``filter()``,
215 ``order_by()``, or even additional calls to ``annotate()``.
216
217 .. _combining-multiple-aggregations:
218
219 Combining multiple aggregations
220 -------------------------------
221
222 Combining multiple aggregations with ``annotate()`` will :ticket:`yield the
223 wrong results <10060>` because joins are used instead of subqueries:
224
225 .. code-block:: pycon
226
227 >>> book = Book.objects.first()
228 >>> book.authors.count()
229 2
230 >>> book.store_set.count()
231 3
232 >>> q = Book.objects.annotate(Count("authors"), Count("store"))
233 >>> q[0].authors__count
234 6
235 >>> q[0].store__count
236 6
237
238 For most aggregates, there is no way to avoid this problem, however, the
239 :class:`~django.db.models.Count` aggregate has a ``distinct`` parameter that
240 may help:
241
242 .. code-block:: pycon
243
244 >>> q = Book.objects.annotate(
245 ... Count("authors", distinct=True), Count("store", distinct=True)
246 ... )
247 >>> q[0].authors__count
248 2
249 >>> q[0].store__count
250 3
251
252 .. admonition:: If in doubt, inspect the SQL query!
253
254 In order to understand what happens in your query, consider inspecting the
255 ``query`` property of your ``QuerySet``.
256
257 Joins and aggregates
258 ====================
259
260 So far, we have dealt with aggregates over fields that belong to the
261 model being queried. However, sometimes the value you want to aggregate
262 will belong to a model that is related to the model you are querying.
263
264 When specifying the field to be aggregated in an aggregate function, Django
265 will allow you to use the same :ref:`double underscore notation
266 <field-lookups-intro>` that is used when referring to related fields in
267 filters. Django will then handle any table joins that are required to retrieve
268 and aggregate the related value.
269
270 For example, to find the price range of books offered in each store,
271 you could use the annotation:
272
273 .. code-block:: pycon
274
275 >>> from django.db.models import Max, Min
276 >>> Store.objects.annotate(min_price=Min("books__price"), max_price=Max("books__price"))
277
278 This tells Django to retrieve the ``Store`` model, join (through the
279 many-to-many relationship) with the ``Book`` model, and aggregate on the
280 price field of the book model to produce a minimum and maximum value.
281
282 The same rules apply to the ``aggregate()`` clause. If you wanted to
283 know the lowest and highest price of any book that is available for sale
284 in any of the stores, you could use the aggregate:
285
286 .. code-block:: pycon
287
288 >>> Store.objects.aggregate(min_price=Min("books__price"), max_price=Max("books__price"))
289
290 Join chains can be as deep as you require. For example, to extract the
291 age of the youngest author of any book available for sale, you could
292 issue the query:
293
294 .. code-block:: pycon
295
296 >>> Store.objects.aggregate(youngest_age=Min("books__authors__age"))
297
298 Following relationships backwards
299 ---------------------------------
300
301 In a way similar to :ref:`lookups-that-span-relationships`, aggregations and
302 annotations on fields of models or models that are related to the one you are
303 querying can include traversing "reverse" relationships. The lowercase name
304 of related models and double-underscores are used here too.
305
306 For example, we can ask for all publishers, annotated with their respective
307 total book stock counters (note how we use ``'book'`` to specify the
308 ``Publisher`` -> ``Book`` reverse foreign key hop):
309
310 .. code-block:: pycon
311
312 >>> from django.db.models import Avg, Count, Min, Sum
313 >>> Publisher.objects.annotate(Count("book"))
314
315 (Every ``Publisher`` in the resulting ``QuerySet`` will have an extra attribute
316 called ``book__count``.)
317
318 We can also ask for the oldest book of any of those managed by every publisher:
319
320 .. code-block:: pycon
321
322 >>> Publisher.objects.aggregate(oldest_pubdate=Min("book__pubdate"))
323
324 (The resulting dictionary will have a key called ``'oldest_pubdate'``. If no
325 such alias were specified, it would be the rather long ``'book__pubdate__min'``.)
326
327 This doesn't apply just to foreign keys. It also works with many-to-many
328 relations. For example, we can ask for every author, annotated with the total
329 number of pages considering all the books the author has (co-)authored (note how we
330 use ``'book'`` to specify the ``Author`` -> ``Book`` reverse many-to-many hop):
331
332 .. code-block:: pycon
333
334 >>> Author.objects.annotate(total_pages=Sum("book__pages"))
335
336 (Every ``Author`` in the resulting ``QuerySet`` will have an extra attribute
337 called ``total_pages``. If no such alias were specified, it would be the rather
338 long ``book__pages__sum``.)
339
340 Or ask for the average rating of all the books written by author(s) we have on
341 file:
342
343 .. code-block:: pycon
344
345 >>> Author.objects.aggregate(average_rating=Avg("book__rating"))
346
347 (The resulting dictionary will have a key called ``'average_rating'``. If no
348 such alias were specified, it would be the rather long ``'book__rating__avg'``.)
349
350 Aggregations and other ``QuerySet`` clauses
351 ===========================================
352
353 ``filter()`` and ``exclude()``
354 ------------------------------
355
356 Aggregates can also participate in filters. Any ``filter()`` (or
357 ``exclude()``) applied to normal model fields will have the effect of
358 constraining the objects that are considered for aggregation.
359
360 When used with an ``annotate()`` clause, a filter has the effect of
361 constraining the objects for which an annotation is calculated. For example,
362 you can generate an annotated list of all books that have a title starting
363 with "Django" using the query:
364
365 .. code-block:: pycon
366
367 >>> from django.db.models import Avg, Count
368 >>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count("authors"))
369
370 When used with an ``aggregate()`` clause, a filter has the effect of
371 constraining the objects over which the aggregate is calculated.
372 For example, you can generate the average price of all books with a
373 title that starts with "Django" using the query:
374
375 .. code-block:: pycon
376
377 >>> Book.objects.filter(name__startswith="Django").aggregate(Avg("price"))
378
379 .. _filtering-on-annotations:
380
381 Filtering on annotations
382 ~~~~~~~~~~~~~~~~~~~~~~~~
383
384 Annotated values can also be filtered. The alias for the annotation can be
385 used in ``filter()`` and ``exclude()`` clauses in the same way as any other
386 model field.
387
388 For example, to generate a list of books that have more than one author,
389 you can issue the query:
390
391 .. code-block:: pycon
392
393 >>> Book.objects.annotate(num_authors=Count("authors")).filter(num_authors__gt=1)
394
395 This query generates an annotated result set, and then generates a filter
396 based upon that annotation.
397
398 If you need two annotations with two separate filters you can use the
399 ``filter`` argument with any aggregate. For example, to generate a list of
400 authors with a count of highly rated books:
401
402 .. code-block:: pycon
403
404 >>> highly_rated = Count("book", filter=Q(book__rating__gte=7))
405 >>> Author.objects.annotate(num_books=Count("book"), highly_rated_books=highly_rated)
406
407 Each ``Author`` in the result set will have the ``num_books`` and
408 ``highly_rated_books`` attributes. See also :ref:`conditional-aggregation`.
409
410 .. admonition:: Choosing between ``filter`` and ``QuerySet.filter()``
411
412 Avoid using the ``filter`` argument with a single annotation or
413 aggregation. It's more efficient to use ``QuerySet.filter()`` to exclude
414 rows. The aggregation ``filter`` argument is only useful when using two or
415 more aggregations over the same relations with different conditionals.
416
417 Order of ``annotate()`` and ``filter()`` clauses
418 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
419
420 When developing a complex query that involves both ``annotate()`` and
421 ``filter()`` clauses, pay particular attention to the order in which the
422 clauses are applied to the ``QuerySet``.
423
424 When an ``annotate()`` clause is applied to a query, the annotation is computed
425 over the state of the query up to the point where the annotation is requested.
426 The practical implication of this is that ``filter()`` and ``annotate()`` are
427 not commutative operations.
428
429 Given:
430
431 * Publisher A has two books with ratings 4 and 5.
432 * Publisher B has two books with ratings 1 and 4.
433 * Publisher C has one book with rating 1.
434
435 Here's an example with the ``Count`` aggregate:
436
437 .. code-block:: pycon
438
439 >>> a, b = Publisher.objects.annotate(num_books=Count("book", distinct=True)).filter(
440 ... book__rating__gt=3.0
441 ... )
442 >>> a, a.num_books
443 (<Publisher: A>, 2)
444 >>> b, b.num_books
445 (<Publisher: B>, 2)
446
447 >>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count("book"))
448 >>> a, a.num_books
449 (<Publisher: A>, 2)
450 >>> b, b.num_books
451 (<Publisher: B>, 1)
452
453 Both queries return a list of publishers that have at least one book with a
454 rating exceeding 3.0, hence publisher C is excluded.
455
456 In the first query, the annotation precedes the filter, so the filter has no
457 effect on the annotation. ``distinct=True`` is required to avoid a :ref:`query
458 bug <combining-multiple-aggregations>`.
459
460 The second query counts the number of books that have a rating exceeding 3.0
461 for each publisher. The filter precedes the annotation, so the filter
462 constrains the objects considered when calculating the annotation.
463
464 Here's another example with the ``Avg`` aggregate:
465
466 .. code-block:: pycon
467
468 >>> a, b = Publisher.objects.annotate(avg_rating=Avg("book__rating")).filter(
469 ... book__rating__gt=3.0
470 ... )
471 >>> a, a.avg_rating
472 (<Publisher: A>, 4.5) # (5+4)/2
473 >>> b, b.avg_rating
474 (<Publisher: B>, 2.5) # (1+4)/2
475
476 >>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(
477 ... avg_rating=Avg("book__rating")
478 ... )
479 >>> a, a.avg_rating
480 (<Publisher: A>, 4.5) # (5+4)/2
481 >>> b, b.avg_rating
482 (<Publisher: B>, 4.0) # 4/1 (book with rating 1 excluded)
483
484 The first query asks for the average rating of all a publisher's books for
485 publisher's that have at least one book with a rating exceeding 3.0. The second
486 query asks for the average of a publisher's book's ratings for only those
487 ratings exceeding 3.0.
488
489 It's difficult to intuit how the ORM will translate complex querysets into SQL
490 queries so when in doubt, inspect the SQL with ``str(queryset.query)`` and
491 write plenty of tests.
492
493 ``order_by()``
494 --------------
495
496 Annotations can be used as a basis for ordering. When you
497 define an ``order_by()`` clause, the aggregates you provide can reference
498 any alias defined as part of an ``annotate()`` clause in the query.
499
500 For example, to order a ``QuerySet`` of books by the number of authors
501 that have contributed to the book, you could use the following query:
502
503 .. code-block:: pycon
504
505 >>> Book.objects.annotate(num_authors=Count("authors")).order_by("num_authors")
506
507 ``values()``
508 ------------
509
510 Ordinarily, annotations are generated on a per-object basis - an annotated
511 ``QuerySet`` will return one result for each object in the original
512 ``QuerySet``. However, when a ``values()`` clause is used to constrain the
513 columns that are returned in the result set, the method for evaluating
514 annotations is slightly different. Instead of returning an annotated result
515 for each result in the original ``QuerySet``, the original results are
516 grouped according to the unique combinations of the fields specified in the
517 ``values()`` clause. An annotation is then provided for each unique group;
518 the annotation is computed over all members of the group.
519
520 For example, consider an author query that attempts to find out the average
521 rating of books written by each author:
522
523 .. code-block:: pycon
524
525 >>> Author.objects.annotate(average_rating=Avg("book__rating"))
526
527 This will return one result for each author in the database, annotated with
528 their average book rating.
529
530 However, the result will be slightly different if you use a ``values()`` clause:
531
532 .. code-block:: pycon
533
534 >>> Author.objects.values("name").annotate(average_rating=Avg("book__rating"))
535
536 In this example, the authors will be grouped by name, so you will only get
537 an annotated result for each *unique* author name. This means if you have
538 two authors with the same name, their results will be merged into a single
539 result in the output of the query; the average will be computed as the
540 average over the books written by both authors.
541
542 Order of ``annotate()`` and ``values()`` clauses
543 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
544
545 As with the ``filter()`` clause, the order in which ``annotate()`` and
546 ``values()`` clauses are applied to a query is significant. If the
547 ``values()`` clause precedes the ``annotate()``, the annotation will be
548 computed using the grouping described by the ``values()`` clause.
549
550 However, if the ``annotate()`` clause precedes the ``values()`` clause,
551 the annotations will be generated over the entire query set. In this case,
552 the ``values()`` clause only constrains the fields that are generated on
553 output.
554
555 For example, if we reverse the order of the ``values()`` and ``annotate()``
556 clause from our previous example:
557
558 .. code-block:: pycon
559
560 >>> Author.objects.annotate(average_rating=Avg("book__rating")).values(
561 ... "name", "average_rating"
562 ... )
563
564 This will now yield one unique result for each author; however, only
565 the author's name and the ``average_rating`` annotation will be returned
566 in the output data.
567
568 You should also note that ``average_rating`` has been explicitly included
569 in the list of values to be returned. This is required because of the
570 ordering of the ``values()`` and ``annotate()`` clause.
571
572 If the ``values()`` clause precedes the ``annotate()`` clause, any annotations
573 will be automatically added to the result set. However, if the ``values()``
574 clause is applied after the ``annotate()`` clause, you need to explicitly
575 include the aggregate column.
576
577 .. _aggregation-ordering-interaction:
578
579 Interaction with ``order_by()``
580 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
581
582 Fields that are mentioned in the ``order_by()`` part of a queryset are used
583 when selecting the output data, even if they are not otherwise specified in the
584 ``values()`` call. These extra fields are used to group "like" results together
585 and they can make otherwise identical result rows appear to be separate. This
586 shows up, particularly, when counting things.
587
588 By way of example, suppose you have a model like this::
589
590 from django.db import models
591
592
593 class Item(models.Model):
594 name = models.CharField(max_length=10)
595 data = models.IntegerField()
596
597 If you want to count how many times each distinct ``data`` value appears in an
598 ordered queryset, you might try this::
599
600 items = Item.objects.order_by("name")
601 # Warning: not quite correct!
602 items.values("data").annotate(Count("id"))
603
604 ...which will group the ``Item`` objects by their common ``data`` values and
605 then count the number of ``id`` values in each group. Except that it won't
606 quite work. The ordering by ``name`` will also play a part in the grouping, so
607 this query will group by distinct ``(data, name)`` pairs, which isn't what you
608 want. Instead, you should construct this queryset::
609
610 items.values("data").annotate(Count("id")).order_by()
611
612 ...clearing any ordering in the query. You could also order by, say, ``data``
613 without any harmful effects, since that is already playing a role in the
614 query.
615
616 This behavior is the same as that noted in the queryset documentation for
617 :meth:`~django.db.models.query.QuerySet.distinct` and the general rule is the
618 same: normally you won't want extra columns playing a part in the result, so
619 clear out the ordering, or at least make sure it's restricted only to those
620 fields you also select in a ``values()`` call.
621
622 .. note::
623 You might reasonably ask why Django doesn't remove the extraneous columns
624 for you. The main reason is consistency with ``distinct()`` and other
625 places: Django **never** removes ordering constraints that you have
626 specified *explicitly with* ``order_by()`` (and we can't change those
627 other methods' behavior, as that would violate our
628 :doc:`/misc/api-stability` policy).
629
630 .. admonition:: Default ordering not applied to GROUP BY
631
632 ``GROUP BY`` queries (for example, those using ``.values()`` and
633 ``.annotate()``) don't use the model's default ordering.
634 Use ``order_by()`` explicitly when a given order is needed.
635
636 Aggregating annotations
637 -----------------------
638
639 You can also generate an aggregate on the result of an annotation. When you
640 define an ``aggregate()`` clause, the aggregates you provide can reference
641 any alias defined as part of an ``annotate()`` clause in the query.
642
643 For example, if you wanted to calculate the average number of authors per
644 book you first annotate the set of books with the author count, then
645 aggregate that author count, referencing the annotation field:
646
647 .. code-block:: pycon
648
649 >>> from django.db.models import Avg, Count
650 >>> Book.objects.annotate(num_authors=Count("authors")).aggregate(Avg("num_authors"))
651 {'num_authors__avg': 1.66}
652
653 Aggregating on empty querysets or groups
654 ----------------------------------------
655
656 When an aggregation is applied to an empty queryset or grouping, the result
657 defaults to its :ref:`default <aggregate-default>` parameter, typically
658 ``None``. This behavior occurs because aggregate functions return ``NULL`` when
659 the executed query returns no rows.
660
661 You can specify a return value by providing the :ref:`default
662 <aggregate-default>` argument for most aggregations. However, since
663 :class:`~django.db.models.Count` does not support the :ref:`default
664 <aggregate-default>` argument, it will always return ``0`` for empty querysets
665 or groups.
666
667 For example, assuming that no book contains *web* in its name, calculating the
668 total price for this book set would return ``None`` since there are no matching
669 rows to compute the :class:`~django.db.models.Sum` aggregation on:
670
671 .. code-block:: pycon
672
673 >>> from django.db.models import Sum
674 >>> Book.objects.filter(name__contains="web").aggregate(Sum("price"))
675 {"price__sum": None}
676
677 However, the :ref:`default <aggregate-default>` argument can be set when
678 calling :class:`~django.db.models.Sum` to return a different default value if
679 no books can be found:
680
681 .. code-block:: pycon
682
683 >>> Book.objects.filter(name__contains="web").aggregate(Sum("price", default=0))
684 {"price__sum": Decimal("0")}
685
686 Under the hood, the :ref:`default <aggregate-default>` argument is implemented
687 by wrapping the aggregate function with
688 :class:`~django.db.models.functions.Coalesce`.