[Django] #28940: Annotate SUM aggregation value as field

[Django]

#28940: Annotate SUM aggregation value as field
-------------------------------------+-------------------------------------
Reporter: marriva | Owner: nobody
Type: | Status: new
Uncategorized |
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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'})

--
Ticket URL: <https://code.djangoproject.com/ticket/28940>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#28940: Annotate SUM aggregation value as field
-------------------------------------+-------------------------------------

Reporter: Vasiliy Maryutenkov | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Vasiliy Maryutenkov:

Old description:

> 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'})

New description:

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?

--

--
Ticket URL: <https://code.djangoproject.com/ticket/28940#comment:1>

[Django]

#28940: Annotate SUM aggregation value as field
-------------------------------------+-------------------------------------

Reporter: Vasiliy Maryutenkov | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (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`
it as well?

--
Ticket URL: <https://code.djangoproject.com/ticket/28940#comment:2>

[Django]

#28940: Annotate SUM aggregation value as field
-------------------------------------+-------------------------------------

Reporter: Vasiliy Maryutenkov | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Vasiliy Maryutenkov):

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

field / total

--
Ticket URL: <https://code.djangoproject.com/ticket/28940#comment:3>

[Django]

#28940: Annotate SUM aggregation value as field
-------------------------------------+-------------------------------------

Reporter: Vasiliy Maryutenkov | Owner: nobody

Type: Uncategorized | Status: closed

Component: Database layer | Version: 1.11
(models, ORM) |

Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* status: new => closed
* resolution: => duplicate

Comment:

If I understand you correctly you're after something along

{{{#!python
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.

{{{#!python
total = Model.objects.aggregate(total=Sum('field'))['total']
queryset = Model.objects.annotate(percentage=F('field')/total)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/28940#comment:4>