I’m trying to achieve the following SQL query in Django:

(The table contains the purchases per each day per each location. I want to get the biggest amount of each location, then group them by state to see how much is the max that can be sold in each state)

select state, type, sum(amount)
    from (
        select l.state, dd.type, max(amount) as amount 
        from daily_data dd
        join locations l on l.id = dd.location_id
        where dd.type in ('purchases', 'returns')
        group by dd.location_id, dd.type
    )
    group by state, type

Where I get this:

NY,purchases,80
NY, returns,6
Maine,purchases,125
Maine, returns,12

But I’m geting stuck on how to achieve that in django.

I tried this:

daily_data.objects.filter(type__in=['purchases', 'returns']
        ).values('location', 'type'
        ).annotate(total=Max('amount')
        ).values('location__state','type'
        ).annotate(sum=Sum('total'))

But I get an error django.core.exceptions.FieldError: Cannot compute Sum('total'): 'total' is an aggregate

I even tried a subquery, but this is generating a bad sql query which yields the query taking forever.

subquery = daily_data.objects.filter(
    location=OuterRef('location'),
    type=OuterRef('type')
).values('location', 'type').annotate(
    max_amount=Max('amount')
).values('max_amount')

annotated_data = daily_data.objects.filter(
    type__in=['purchases', 'returns']
).annotate(
    max_amount=Subquery(subquery)
).values(
    'location__state', 'type'
).annotate(
    total_max_amount=Sum('max_amount')
).order_by('location__state', 'type')

This generates:

SELECT "state", "type", SUM((
        SELECT MAX(U0."amount") AS "total" FROM "daily_data" U0
        INNER JOIN "locations" U1 ON (U0."location_id" = U1."id")
        WHERE (U1."state" = ("state") AND U2."type" = "type") GROUP BY U0."location_id", U2."type")) AS "total"
FROM "daily_data"
    INNER JOIN "locations" ON ("location_id" = "locations"."id") WHERE where "type" in ('purchases', 'returns')
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             GROUP BY "state", "type"

which is redundant and expensive… And I didn’t get to see the result yet as it’s loading forever…