CODEWITHSUNDEEP
pythondjangodjango-1.8
Cory Madden
Cory Madden

Reputation: 5193

Any way to group aggregations in Django?

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.

Upvotes: 1

Views: 130

Answers (1)

JPG
JPG

Reputation: 88489

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 ?

Upvotes: 1

Related Questions