I have a parent table **Order** whose primary key is the foreign key of the two child tables **Item** and **Payment**

**Item**

```
class Item(models.Model):
item_name = models.CharField(max_length=200, null=True)
order = models.ForeignKey(Order, on_delete=models.CASCADE)
weight = models.DecimalField(null=True, max_digits=10, decimal_places=3)
rate_per_gram = models.DecimalField(null=True, max_digits=10, decimal_places=2)
charge_per_gram = models.DecimalField(null=True, max_digits=10, decimal_places=2)
making_charge = models.DecimalField(null=True, max_digits=10, decimal_places=2)
total = models.DecimalField(null=True, max_digits=10, decimal_places=2)
def __str__(self):
return self.item_name
```

and

**Payment**

```
class Payment(models.Model):
order = models.ForeignKey(Order, on_delete=models.CASCADE)
created_at = models.DateField(auto_now_add=True)
updated_at = models.DateField(auto_now=True)
amount = models.DecimalField(null=True, max_digits=10, decimal_places=2)
remarks = models.CharField(max_length=200, null=True)
def __str__(self):
return self.amount
```

What I want to get is a list of all Orders along with the **Sum(total) from Item table** and **Sum(amount) from Payment table**

I am unable to figure out how to write the code in django ORM:

I have written something like this but the summation results are wrong:

```
order_list = Order.objects.values('id').annotate(
amount_paid=Sum('payment__amount'),
order_value=Sum('item__total')
).order_by('-id')
```

MySql query is as follows:

```
select app_order.id,
(select sum(app_payment.amount) from app_payment
where app_order.id=app_payment.order_id) as total_amount_paid,
(select sum(app_item.total) from app_item
where app_order.id=app_item.order_id) as total_order_amount
from app_order;
```

The feature you’re looking for is Aggregation. In particular, see the examples in the sections on Joins and aggregates.

I checked the docs and this is what i tried:

```
order_list = Order.objects.values('id').annotate(
amount_paid=Sum('payment__amount'),
order_value=Sum('item__total')
).order_by('-id')
```

The summation result are incorrect. Kindly help me figure out where I am doing it wrong.

The item table has three rows with same order_id and the `sum('item__total')`

result I’m getting is Decimal(‘55633.60’)

```
+----+-------------+--------+---------------+-----------------+---------------+----------+----------+-------------+
| id | description | weight | rate_per_gram | charge_per_gram | making_charge | total | order_id | item_name |
+----+-------------+--------+---------------+-----------------+---------------+----------+----------+-------------+
| 1 | 918 BS | 10.980 | 1290.00 | 245.00 | 2690.10 | 16854.30 | 47 | Gents Ring |
| 2 | 924 BIS | 2.890 | 1290.00 | 544.00 | 1572.16 | 5300.26 | 47 | Ladies Ring |
| 3 | 924 BIS | 3.440 | 1290.00 | 356.00 | 1224.64 | 5662.24 | 47 | Gents Ring |
+----+-------------+--------+---------------+-----------------+---------------+----------+----------+-------------+
```

The payment table has two rows with same order_id and the `sum('payment__amount')`

result I’m getting is Decimal(‘45000.00’)

```
+----+----------+-----------+----------+------------+------------+
| id | amount | remarks | order_id | created_at | updated_at |
+----+----------+-----------+----------+------------+------------+
| 1 | 10000.00 | UNCLEARED | 47 | 2021-05-16 | 2021-05-16 |
| 2 | 5000.00 | Cash | 47 | 2021-05-16 | 2021-05-16 |
+----+----------+-----------+----------+------------+------------+
```

Those totals you’re seeing, 55633.60 for Item and 45000 for Payment, are they the complete sums for those tables? (Implying that the summation is not filtering by id.)

I would initially suggest removing the `values`

clause.

No, This is the only data that I have in those tables. *What I’m able to figure out is that the Sum(total) of item is multiplied by the row count of the payment table i.e. 2 and the Sum(amount) of payment table is multiplied by the row count of the item table i.e. 3 having same order_id.*

I tried removing the `values`

clause

```
order_list = Order.objects.annotate(
amount_paid=Sum('payment__amount'),
order_value=Sum('item__total')
).order_by('-id')
for order in order_list:
amount_paid=order.amount_paid
order_value=order.order_value
print(amount_paid, order_value)
```

but the results are same:

`45000.00 55633.60`

See the section Combining multiple aggregations, and if you want to see a lot of background information on this issue, see Aggregation | Django documentation | Django

So with the ability to code Subqueries, you could code this as annotating your query with the sum of a subquery.

Kinda doing this off the cuff, I’d give something like this a try (I’d say the chance that this works is about 50-50):

```
total_query = Subquery(Item.objects.filter(order=OuterRef('id')).values('total'))
amount_query = Subquery(Payment.objects.filter(order=OuterRef('id')).values('amount'))
order_list = Order.objects.annotate(amount_paid=Sum(amount_query), order_value=Sum(total_query))
```