I'm trying to construct a query to get the "average, maximum, minimum number of items purchased by a single user".

The data source is this simple sales record table:

class SalesRecord(models.Model):
    id           = models.IntegerField(primary_key=True)
    user_id      = models.IntegerField()
    product_code = models.CharField()
    price        = models.IntegerField()
    created_at   = models.DateTimeField()

A new record is inserted into this table for every item purchased by a user. Here's my attempt at building the query:

q = SalesRecord.objects.all()
q = q.values('user_id').annotate(   # group by user and count the # of records
    count=Count('id'),              # (= # of items)
    ).order_by()
result = q.aggregate(Max('count'), Min('count'), Avg('count'))

When I try to execute the code, a ProgrammingError is raised at the last line:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT sales_records.user_id AS user_id, COUNT(sales_records.`' at line 1")

Django's error screen shows that the SQL is

SELECT FROM
  (SELECT
    `sales_records`.`player_id` AS `player_id`,
    COUNT(`sales_records`.`id`) AS `count`
  FROM `sales_records`
  WHERE (`sales_records`.`created_at` >= %s AND `sales_records`.`created_at` <= %s )
  GROUP BY `sales_records`.`player_id` ORDER BY NULL) subquery

It's not selecting anything! Can someone please show me the right way to do this?

Hacking Django

I've found that clearing the cache of selected fields in django.db.models.sql.BaseQuery.get_aggregation() seems to solve the problem. Though I'm not really sure this is a fix or a workaround.

@@ -327,10 +327,13 @@
    # Remove any aggregates marked for reduction from the subquery
    # and move them to the outer AggregateQuery.
+   self._aggregate_select_cache = None
+   self.aggregate_select_mask = None
    for alias, aggregate in self.aggregate_select.items():
        if aggregate.is_summary:
            query.aggregate_select[alias] = aggregate
-           del obj.aggregate_select[alias]
+           if alias in obj.aggregate_select:
+               del obj.aggregate_select[alias]

... yields result:

{'count__max': 267, 'count__avg': 26.2563, 'count__min': 1}