Dates & standard aggregates (Count/Avg/Min/Max/Sum, ExtractYear)
aggregate() collapses the whole queryset into a single row of numbers.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | # ====================================================================== # `aggregate()` collapses the whole queryset into a single row of numbers. To # get one row *per group* instead, use `values(...)` (the GROUP BY) followed by # `annotate(...)`. `ExtractYear` pulls the year out of a datetime so we can # count books per year. # ====================================================================== # ---------------------------------------------------------------------- # aggregate over all books # ---------------------------------------------------------------------- # Django: Book.objects.aggregate( n=Count("id"), avg=Avg("price"), lo=Min("price"), hi=Max("price"), total=Sum("price"), ) # SQL: # SELECT COUNT("examples_book"."id") AS "n", AVG("examples_book"."price") AS "avg", MIN("examples_book"."price") AS "lo", MAX("examples_book"."price") AS "hi", SUM("examples_book"."price") AS "total" # FROM "examples_book" # Result: # n=4 avg=15.87 min=0.00 max=29.00 total=63.49 # ---------------------------------------------------------------------- # books per publication year # ---------------------------------------------------------------------- # Django: Book.objects.annotate(y=ExtractYear("published")) \ .values("y").annotate(n=Count("id")).order_by("y") # SQL: # SELECT EXTRACT(YEAR # FROM "examples_book"."published" AT TIME ZONE 'UTC') AS "y", COUNT("examples_book"."id") AS "n" # FROM "examples_book" # GROUP BY 1 # ORDER BY 1 ASC # Result: # 1937: 1 # 1954: 1 # 1997: 1 # 2026: 1 |