minzey minzey - 4 months ago 12
SQL Question

Sum same column in different ways depending on other column - Django ORM

I have the following table assignment structure:

| employee | product | process | qty |
| Swati | PROD1 | issue | 60 |
| Rohit | PROD1 | issue | 30 |
| Rohit | PROD2 | issue | 40 |
| Swati | PROD1 | receive | 40 |
| Swati | PROD2 | issue | 70 |


I want the final table to look like this for each employee (say for
employee = 'Swati'
):

| product | sum_issued | sum_received
| PROD1 | 60 | 40 |
| PROD2 | 70 | 0 |


The SQL query which does this is:

select product
, sum(case when process='issue' then qty else 0 end) as sum_issued
, sum(case when process='receive' then qty else 0 end) as sum_received
from assignment
where employee = 'Swati'
group
by product;


What should the Django query be, corresponding to this result?

Answer

I guess your Model name is 'Assignment'. You can use the below query

from django.db.models import Case, Value, When, Sum, IntegerField, Count

result = Assignment.objects.filter(employee="Swati").values('product').annotate(
    sum_issued=Sum(
        Case(When(process='issue', then='qty'), default=Value(0), output_field=IntegerField())),
    sum_recived=Sum(Case(When(process='receive', then='qty'), default=Value(0), output_field=IntegerField()))
    )

If you print the above query print result.query , the result is,

SELECT "product", SUM(CASE WHEN "process" = issue THEN "qty" ELSE 0 END) AS "sum_issued", SUM(CASE WHEN "process" = receive THEN "qty" ELSE 0 END) AS "sum_recived" FROM "assignment" WHERE "employee" = 'Swati' GROUP BY "product"
Comments