Django: Group by date (day, month, year)
django, django-aggregation
Solution
Django 1.10 and above
Django documentation lists `extra` as deprecated soon. (Thanks for pointing that out @seddonym, @Lucas03). I opened a ticket and this is the solution that jarshwah provided.
from django.db.models.functions import TruncMonth
from django.db.models import Count
Sales.objects
.annotate(month=TruncMonth('created')) # Truncate to month and add to select list
.values('month') # Group By month
.annotate(c=Count('id')) # Select the count of the grouping
.values('month', 'c') # (might be redundant, haven't tested) select month and count
Older versions
from django.db import connection
from django.db.models import Sum, Count
truncate_date = connection.ops.date_trunc_sql('month', 'created')
qs = Order.objects.extra({'month':truncate_date})
report = qs.values('month').annotate(Sum('total'), Count('pk')).order_by('month')
Edits
- Added count
- Added information for django >= 1.10
Problem
I've got a simple Model like this: ``` class Order(models.Model): created = model.DateTimeField(auto_now_add=True) total = models.IntegerField() # monetary value ``` And I want to output a month-by-month breakdown of: - How many sales there were in a month (`COUNT`) - The combined value (`SUM`) I'm not sure what the best way to attack this is. I've seen some fairly scary-looking extra-select queries but my simple mind is telling me I might be better off just iterating numbers, starting from an arbitrary start year/month and counting up until I reach the current month, throwing out simple queries filtering for that month. More database work - less developer stress! What makes most sense to you? Is there a nice way I can pull back a quick table of data? Or is my dirty method probably the best idea? I'm using Django 1.3. Not sure if they've added a nicer way to `GROUP_BY` recently.