django-aggregation - w3toppers.com

Using .aggregate() on a value introduced using .extra(select={…}) in a Django Query?

You could use a custom aggregate function to produce your query: WEEK_FUNC = ‘STRFTIME(“%%%%W”, %s)’ # use ‘WEEK(%s)’ for mysql class WeekCountAggregate(models.sql.aggregates.Aggregate): is_ordinal = True sql_function = ‘WEEK’ # unused sql_template = “COUNT(%s)” % (WEEK_FUNC.replace(‘%%’, ‘%%%%’) % ‘%(field)s’) class WeekCount(models.aggregates.Aggregate): name=”Week” def add_to_query(self, query, alias, col, source, is_summary): query.aggregates[alias] = WeekCountAggregate(col, source=source, is_summary=is_summary, **self.extra) >>> … Read more

How to filter objects for count annotation in Django?

Conditional aggregation in Django 2.0 allows you to further reduce the amount of faff this has been in the past. This will also use Postgres’ filter logic, which is somewhat faster than a sum-case (I’ve seen numbers like 20-30% bandied around). Anyway, in your case, we’re looking at something as simple as: from django.db.models import … Read more

How to group by AND aggregate with Django

Try this: Rating.objects.filter(attribute__in=attributes) \ .values(‘location’) \ .annotate(score = Sum(‘score’)) \ .order_by(‘-score’)

Django 1.11 Annotating a Subquery Aggregate

Shazaam! Per my edits, an additional column was being output from my subquery. This was to facilitate ordering (which just isn’t required in a COUNT). I just needed to remove the prescribed meta-order from the model. You can do this by just adding an empty .order_by() to the subquery. In my code terms that meant: … Read more

Django: Group by date (day, month, year)

Django 1.10 and above Django documentation lists extra as deprecated soon. (Thanks for pointing that out @seddonym, @Lucas03). I opened a ticket and this is the solution that jarshwah provided. from django.db.models.functions import TruncMonth from django.db.models import Count Sales.objects .annotate(month=TruncMonth(‘created’)) # Truncate to month and add to select list .values(‘month’) # Group By month .annotate(c=Count(‘id’)) … Read more