I have data like this
| product | type | quantity | price/quantity |
|---|---|---|---|
| chocolate | buy | 2 | 100 |
| chocolate | buy | 4 | 200 |
| colddrink | buy | 3 | 300 |
| chocolate | sell | 3 | 200 |
| colddrink | buy | 1 | 100 |
now I want to group the data by product and its type, and the quantity should be equal to total quantity for the particular type. While total price should be equal to (price(at which it was bought/sold) * quantity)
for eg. aggregated result should be like for above table
| product | type | quantity | total price |
|---|---|---|---|
| chocolate | buy | 6 | 1000 |
| chocolate | sell | 3 | 600 |
| colddrink | buy | 4 | 1000 |
so far I've tried to do this
Product.objects.values('product', 'type').annotate(quantity = Sum('quantity'),price = Sum('price', field='price*quantity)
it would return
<QuerySet [{'product': 'chocolate', 'type': 'buy', 'quantity': 6, 'price': 300.0}, {'product': 'chocolate', 'type': 'sell', 'quantity': 3, 'price': 200.0}, {'product': 'colddrink', 'type': 'buy', 'quantity': 4, 'price': 400.0}]>
As you can see, it just sums the price instead of multiplying with quantity. Anything I'm doing wrong, or anyone can suggest how to do this?
