[Answered ]-Kind of complex query in Django with annotate

by

2đź‘Ť

âś…

Summing Revenues in the same currency

If all the revenue numbers are in the same currency, you can do this using annotate and Avg.

job_titles = JobTitle.objects.filter(isActive=True
                              ).annotate(employer__count=Count('employer'), 
                                         average_revenue=Avg('employer__revenue'),
                              ).order_by('employer__count')[:5]

Summing Revenues in different currencies

In the comments you mention you’d like to convert revenues from GBP and EUR then sum in USD. I don’t have an elegant solution to this I’m afraid.

One approach is to use values() to get the sums in each currency:

# look at the employers in the top job titles
employers = Employer.objects.filter(job_title=job_titles)
currency_totals = employers.values('job_title', 'currency', Count('revenue'), Sum('revenue'))

This will return a ValuesQuerySet, for example 

[{'job_title':1, 'currency': 'GBP', 'revenue__sum': 20.0, 'revenue__count': 1},
 {'job_title':1, 'currency': 'EUR', 'revenue__sum': 35.0, 'revenue__count': 2},
 {'job_title':2, 'currency': 'USD', 'revenue__sum': 50.0, 'revenue__count': 1},
 ...
]

You can then loop through the results in the view, converting each revenue__sum to USD and adding to a running total for that job title.

This way, you only need one query to get the revenue sums for a given set of job_titles. However, because we process the results in the view, you can’t get the database to filter on, or order by the revenue calculations e.g. “show me job titles with equivalent revenue greater than USD 1000”.

I’d be interested to see any other approaches — we have customers paying us in GBP, EUR and USD, so we need to generate similar reports.

👤Alasdair

Leave a comment