Dates & standard aggregates (Count/Avg/Min/Max/Sum, ExtractYear)

# ======================================================================
# `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