Django: Sum on an date attribute grouped by month/year

Question

Hello, I'd like to put this query from SQL to Django:

"select date_format(date, '%Y-%m') as month, sum(quantity) as hours from hourentries group by date_format(date, '%Y-%m') order by date;"

The part that causes problem is to group by month when aggregating. I tried this (which seemed logical), but it didn't work :

HourEntries.objects.order_by("date").values("date__month").aggregate(Sum("quantity"))

Answer 1

aggregate can only generate one aggregate value.

You can get the aggregate sum of Hours of the current month by the following query.

from datetime import datetime
this_month = datetime.now().month
HourEntries.objects.filter(date__month=this_month).aggregate(Sum("quantity"))

So, to obtain the aggregate values of HourEntry's all the months, you can loop over the queryset for all the months in the db. But it is better to use the raw sql.

HourEntries.objects.raw("select date_format(date, '%Y-%m') as month, sum(quantity) as hours from hourentries group by date_format(date, '%Y-%m') order by date;")

Answer 2

I guess you cannot aggregate on "quantity" after using values("date__month"), as this leaves only "date" and "month" in the QuerySet.