Group by 2 fields combination and then order by the sum of each group, multiple annotations django

Question

I was trying to get top products ordered by their total margin sum from invoices as:

• Filter invoices by given store_id
• Group by product_name
• And the get the Sum of gross_margin of each group
• Finally order them by Sum of gross_margin (high to low)

Wrong previous code:

high_margin = StoreInvoiceBreakup.objects \
  .filter(store_invoice__store_id=store_id) \   
  .annotate(product_count=Count('product_name')) \
  .annotate(gross_margin_sum=Sum('gross_margin')) \
  .order_by('-gross_margin_sum') \
  .values(name=F('product_name')) \
  .distinct()[:int(top_count)]

And then ended up solving multiple annotation problem via Stack Overflow similar question as:
(as previous code was giving wrong results.)

New correct code:

high_margin = StoreInvoiceBreakup.objects \
  .filter(store_invoice__store_id=store_id) \
  .values('product_name') \
  .annotate(gross_margin_sum=Sum('gross_margin')) \
  .order_by('gross_margin_sum') \
  .distinct()[:int(sell_range)]

And the output looks like:

"high_margin ": [{
  "product_name": "ABCD",
  "gross_margin_sum": 100  --sum of all margins for abcd
}...other products with sum]

Which is absolutely correct, but encountered another problem, as:
A store can have a product with same product_name, but may have different expiry date.
So what i want is,

• To group products by their product_name and expiry_date combination.
• Then get the margin sum for each group and return ordered by the Sum, with distinct combinations. (Not distinct product names only.)
  

*Removing distinct does't help

Or a MySQL query via cursor to do so would also be helpful. If can't do it via queryset as by docs.
The invoice breakup data looks like:

  name  | margin  | ... | total | tax | etc..
  abcd  | 50      | ... |
  abcd  | 50      | ... |
  abcd  | 15      | ... |
  abcd  | 15      | ... |

And output shall be like:

"high_margin ": [{
  "product_name": "ABCD",
  "gross_margin_sum": 100  --sum for 2018 and abcd
  "expiry_date": 2018
},
{
  "product_name": "ABCD",
  "gross_margin_sum": 30  --sum for 2017 and abcd
  "expiry_date": 2017
},... other products with sum and expiry_date]

And the StoreProducts looks like:

  name  |  expiry_date  | size | price | etc...
  abcd  |  2018         |
  abcd  |  2017         |
  xyz   |  2019         |
  pqrs  |  2017         |

• Margin only exist in invoice and not in store products.
• Expity date only exist in store products and not in invoice
• Also I want to be able to change final output names.
• Invoice_product is mapped via foreign_key to store_product_mapping, which then is mapped to master_product.

Maybe SQL query look like:

SELECT NAME, EXPIRY_DATE, SUM(GROSS_MARGIN)
FROM INVOICE_BREAKUP GROUP BY NAME, EXPIRY_DATE
WHERE STORE_ID = 1

*not the actual flow

Answer 1

print(str(high_margin.query))

This way you can get what a queryset produces and play around as its a lazy query as stated by answer here. So I got expected SQL query by just adding the expiry_date in the values, that produces group by name, expiry date as stated by docs.
Code changes as:

high_margin = StoreInvoiceBreakup.objects \
  .filter(store_invoice__store_id=store_id) \
  .values('product_name', 'expiry_date') \
  .annotate(gross_margin_sum=Sum('gross_margin')) \
  .order_by('gross_margin_sum') \
  .distinct()[:int(sell_range)]

*not sure if actually correct