Opened 4 years ago

Closed 4 years ago

#28940 closed Uncategorized (duplicate)

Annotate SUM aggregation value as field

Reported by: Vasiliy Maryutenkov Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Vasiliy Maryutenkov)

Hello

How can I do this in Django without .extra() method?

SELECT SUM(column_1) AS total, column_1, column_2 FROM table

In your documentation you said that I should create the ticket if I use .extra() method, so I'm here

Model.objects.extra(select={'total': 'select sum(field) from model'})

And I also need to use this value in annotation, but it is not possible now :(

.annotate(q=F('field')/F('total'))

How can I do this?

Change History (4)

comment:1 Changed 4 years ago by Vasiliy Maryutenkov

Description: modified (diff)

comment:2 Changed 4 years ago by Simon Charette

Using aggregate functions without a GROUP BY clause makes little sense, what do you expect SUM(field) to return if you plan on selecting field as well?

Last edited 4 years ago by Simon Charette (previous) (diff)

comment:3 Changed 4 years ago by Vasiliy Maryutenkov

I need the total value of 'field' for percentage calculation

field / total

comment:4 Changed 4 years ago by Simon Charette

Resolution: duplicate
Status: newclosed

If I understand you correctly you're after something along

queryset = Model.objects.annotate(
    total=Subquery(Model.objects.aggregate(Sum('field')),
    percent=F('field')/F('total'),
)

But that won't be possible until #28296 gets fixed.

In the mean time you can work around the issue by performing two queries. A first one to retrieve the aggregated sum and a second one to annotate the percentage on rows.

total = Model.objects.aggregate(total=Sum('field'))['total']
queryset = Model.objects.annotate(percentage=F('field')/total)
Note: See TracTickets for help on using tickets.
Back to Top