Django: date attribute amount grouped by month / year

Question

I would like to put this query from SQL in 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 the problem is the monthly grouping during aggregation. I tried this (which seemed logical), but it did not 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 total hours of the current month at the following request.

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

So, to get aggregated HourEntry values ​​for all months, you can program the query for all months in db. But it is better to use 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 think you cannot aggregate the "quantity" after using values("date__month") , since this leaves only the "date" and "month" in the QuerySet.