CODEWITHSUNDEEP
djangodjango-queryset
Alex Lord Mordor
Alex Lord Mordor

Reputation: 3040

Django Group_By and SUM query

I want to make a sumarized inventory report. To make that possible I have my model like this:

class Inventario(models.Model):
    producto = models.ForeignKey(Producto)
    cantidad = models.DecimalField(default=0,decimal_places=2, max_digits=10)
    ubicacion = models.ForeignKey(Ubicacion, null=True, blank=True)
    def __unicode__(self):
        return self.producto.item

In SQL-RAW i will make something like SELECT producto.nombre as item, sum(cantidad) as cantidad,... FROM Inventario GROUP BY item with the correct JOIN of course

And I will obtain something like:

|Item------- | Cantidad|
|PROD1       | 20      |
|PROD2       | 10      |

Instead of

|Item------- | Cantidad|
|PROD1       | 5       |
|PROD1       | 5       |
|PROD1       | 10      |
|PROD2       | 9       |
|PROD2       | 1       |

I want to make it using django way, actually I have

productos = Inventario.objects.filter(cantidad__gt=0).aggregate(Sum('cantidad'))

And this returns me only cantidad_sum:30

UPDATE I get now the GROUP BY with this code

query = Inventario.objects.all().query
query.group_by = ['inventario_inventario.producto_id']
productos = QuerySet(query=query, model=Inventario)

but now I dont know how to make the "SUM" function :/

Upvotes: 1

Views: 1130

Answers (1)

Sohan Jain
Sohan Jain

Reputation: 2377

Try using annotate instead of aggregate. aggregate summarizes the entire queryset, whereas annotate generates values for each group. E.g.,

productos = Inventario.objects.filter(cantidad__gt=0).values('producto').annotate(sum_cantidad=Sum('cantidad'))

Check out the django docs for more details.

Upvotes: 4

Related Questions