1

We're currently using Django 1.8 and Postgres.

I have an aggregation that I want to perform, but I want the aggregations grouped by month. This is trivial to do in SQL, but I can't seem to figure out any way to go about it with the Django ORM.

Here's an example of the SQL query I'm performing that provides the desired results(SQL Fiddle):

SELECT 
    EXTRACT(month from date) as month,
    EXTRACT(year from date) as year,
    SUM(total) as total
FROM transaction
GROUP BY year, month ORDER BY year, month;

And here's an example of my translation to Django(this one is using the Month class from this answer, but I've tried several variations):

results = Transactions.all().annotate(month=Month('date')).aggregate(total=Sum('total', output_field=DecimalField()))

There's some additional aggregating going on here, but I removed it for clarity. I don't care what the Django output would end up looking like as long as it's grouped by month.

Cory Madden
  • 5,026
  • 24
  • 37
  • Annotating with an aggregator should group. You can try `...annotate(month=Month('date'), month_count=Count('date__month'))` – Oluwafemi Sule Feb 28 '18 at 02:20
  • Thanks for the suggestion, but that doesn't work. It won't allow me to query the month from the date with the `date__month`. Says `Cannot resolve keyword 'month' into field. Join on 'date' not permitted.`. I tried using `Month` inside `Count`, but that just provides the same results I was originally getting, which are all of the months in one result. – Cory Madden Feb 28 '18 at 02:47
  • In the answer you linked, the other guy uses `annotate` to generate the months, `values` to generate a list of those months, and then `annotate` to attach the `Sum()` to those months. Is there a reason why you've changed to `aggregate` in your answer? – VMatić Feb 28 '18 at 03:11
  • @pocketkings Annotate just provides a list of the actual `Transaction` objects, and doesn't provide me aggregated values like I'm trying to get. – Cory Madden Feb 28 '18 at 04:28

1 Answers1

1

Try this

results=Transactions.objects.annotate(month=Month('date'),year = Year('date')).values('month','year').annotate(total=Sum('total', output_field=DecimalField())).order_by('year','month')


You can see the raw sql query for the corresponding ORM by,

print(results.query)


and it would provide a result as 

[
    {'month': 1, 'year': 2017, 'total': 139522},
    {'month': 2, 'year': 2017, 'total': 560086},
    {'month': 3, 'year': 2017, 'total': 1292125},
    {'month': 1, 'year': 2018, 'total': 77058413},
    {'month': 2, 'year': 2018, 'total': 99205278},
]


Is that you looking for ?

JPG
  • 82,442
  • 19
  • 127
  • 206
  • Thanks, Jerin. That's great, but it's kinda ugly and has me concerned about how exactly Django is making the query. I'll have to run some tests since I can't run an explain on that aggregation(or can I?). Otherwise I think I may just stick with the SQL since it's clearer what's happening, IMO. – Cory Madden Feb 28 '18 at 15:48
  • The initial annotation is something like `AS` in SQL. Then the `values()` has the attributes that are going to display and the next annotation provide the aggregation functionality. – JPG Feb 28 '18 at 16:02
  • If I am not wrong, a `values()` followed by `annotation()` would perform `Group By` operation – JPG Feb 28 '18 at 16:04
  • I just ran an explain using this:https://stackoverflow.com/a/39168237/4832296 answer and an explain in SQL and they come out to be the same. So now I have to run some tests with the other aggregations, but I assume the SQL planner knows what to do. Thanks. – Cory Madden Feb 28 '18 at 16:37