Django select distinct sum

Question

I have the following (greatly simplified) table structure:

Order:
    order_number = CharField
    order_invoice_number = CharField
    order_invoice_value = CharField

An invoice number can be identical on more than one order (order O1 has invoice number I1, order O2 has invoice number I1, etc.). All the orders with the same invoice number have the same invoice value.

For example:

Order no.    Invoice no.    Value
O1           I1             200
O2           I1             200
O3           I1             200
04           I2             50
05           I3             100

What I am trying to do is do a sum over all the invoice values, but don't add the invoices with the same number more than once. The sum for the above items would be: 200+50+100.

I tried doing this using

s = orders.values('order_invoice_id').annotate(total=Sum('order_invoice_value')).order_by() 

and

s = orders.values('order_invoice_id').order_by().annotate(total=Sum('order_invoice_value'))

but I didn't get the desired result. I tried a few different solutions from similar questions around here but I couldn't get the desired result.

I can't figure out what I'm doing wrong and what I actually should do to get a sum that uses each invoice value just once.

Edit:

I just realized that at O5 I wrote I2 instead of I3. It's written properly now.

Desired result for the data above is the sum of each invoice value, used only once. Sum = I1+I2+I3 = 200+50+100 = 350

Answer 1

I would say that your model structure is wrong. You should have one table for Order, containing the order number and value, and a separate related table for the invoices, with a foreign key from Invoice to Order. Then your query would be simple.