Django: Query Group By Month

Question

How to calculate total by month without using extra?

I'm currently using:

• django 1.8
• postgre 9.3.13
• Python 2.7

Example.

What I have tried so far.

#Doesn't work for me but I don't mind because I don't want to use extra
truncate_month = connection.ops.date_trunc_sql('month','day')
invoices = Invoice.objects.filter(is_deleted = False,company = company).extra({'month': truncate_month}).values('month').annotate(Sum('total'))

----
#It works but I think that it's too slow if I query a big set of data
for current_month in range(1,13):
    Invoice.objects.filter(date__month = current__month).annotate(total = Sum("total"))

and also this one, the answer seems great but I can't import the TruncMonth module.

Django: Group by date (day, month, year)

---

P.S. I know that this question is already asked multiple times but I don't see any answer.

Thanks!

---

SOLUTION:

Thanks to @Vin-G's answer.

Answer 1

First, you have to make a Function that can extract the month for you:

from django.db import models
from django.db.models import Func

class Month(Func):
    function = 'EXTRACT'
    template = '%(function)s(MONTH from %(expressions)s)'
    output_field = models.IntegerField()

After that all you need to do is

1. annotate each row with the month
2. group the results by the annotated month using values()
3. annotate each result with the aggregated sum of the totals using Sum()

Important: if your model class has a default ordering specified in the meta options, then you will have to add an empty order_by() clause. This is because of https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#interaction-with-default-ordering-or-order-by

Fields that are mentioned in the order_by() part of a queryset (or which are used in the default ordering on a model) are used when selecting the output data, even if they are not otherwise specified in the values() call. These extra fields are used to group “like” results together and they can make otherwise identical result rows appear to be separate.

If you are unsure, you could just add the empty order_by() clause anyway without any adverse effects.

i.e.

from django.db.models import Sum

summary = (Invoice.objects
              .annotate(m=Month('date'))
              .values('m')
              .annotate(total=Sum('total'))
              .order_by())

See the full gist here: https://gist.github.com/alvingonzales/ff9333e39d221981e5fc4cd6cdafdd17

If you need further information:

Details on creating your own Func classes: https://docs.djangoproject.com/en/1.8/ref/models/expressions/#func-expressions

Details on the values() clause, (pay attention to how it interacts with annotate() with respect to order of the clauses): https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#values

the order in which annotate() and values() clauses are applied to a query is significant. If the values() clause precedes the annotate(), the annotation will be computed using the grouping described by the values() clause.

Answer 2

result = (
    invoices.objects
        .all()
        .values_list('created_at__year', 'created_at__month')
        .annotate(Sum('total'))
        .order_by('created_at__year', 'created_at__month')
)

Answer 3

itertools.groupby is the performant option in Python and can be utilized with a single db query:

from itertools import groupby

invoices = Invoice.objects.only('date', 'total').order_by('date')
month_totals = {
    k: sum(x.total for x in g) 
    for k, g in groupby(invoices, key=lambda i: i.date.month)
}
month_totals
# {1: 100, 3: 100, 4: 500, 7: 500}

I am not aware of a pure django ORM solution. The date__month filter is very limited and cannot be used in values, order_by, etc.

Answer 4

Don't forget that Django querysets provide a native datetimes manager, which lets you easily pull all of the days/weeks/months/years out of any queryset for models with a datetime field. So if the Invoice model above has a created datetime field, and you want totals for each month in your queryset, you can just do:

    invoices = Invoice.objects.all()
    months = invoices.datetimes("created", kind="month")
    for month in months:
        month_invs = invoices.filter(created__month=month.month)
        month_total = month_invs.aggregate(total=Sum("otherfield")).get("total")
        print(f"Month: {month}, Total: {month_total}")

No external functions or deps needed.

Answer 5

I don't know if my solution is faster than your. You should profile it. Nonetheless I only query the db once instead of 12 times.

#utils.py
from django.db.models import Count, Sum


def get_total_per_month_value():
    """
    Return the total of sales per month

    ReturnType: [Dict]
    {'December': 3400, 'February': 224, 'January': 792}
    """
    result= {}
    db_result = Sale.objects.values('price','created')
    for i in db_result:
        month = str(i.get('created').strftime("%B"))
        if month in result.keys():
            result[month] = result[month] + i.get('price')
        else:
            result[month] = i.get('price')
    return result

#models.py
class Sale(models.Model):
    price = models.PositiveSmallIntegerField()
    created = models.DateTimeField(_(u'Published'), default="2001-02-24")

#views.py
from .utils import get_total_per_month_value
# ...
result = get_total_per_month_value()

test.py

  #
    import pytest
    from mixer.backend.django import mixer
    #Don't try to write in the database
    pytestmark = pytest.mark.django_db
    def test_get_total_per_month():
        from .utils import get_total_per_month_value
        selected_date = ['01','02','03','01','01']
        #2016-01-12 == YYYY-MM-DD
        for i in selected_date:
            mixer.blend('myapp.Sale', created="2016-"+i+"-12")
        values = get_total_per_month_value() #return a dict
        months = values.keys()
        assert 'January' in months, 'Should include January'
        assert 'February' in months, 'Should include February'
        assert len(months) == 3, 'Should aggregate the months'

Answer 6

I have a reservation models which have fields like booked date, commission amount, total booking amount etc. and based on the year provided I have to aggregate the reservations by months. Here is how I did that:

from django.db.models import Count, Sum
from django.db.models.functions import ExtractMonth

Reservation.objects.filter(
    booked_date__year=year
).values(
    'id',
    'booked_date',
    'commission_amount',
    'total_amount'
).annotate(
    month=ExtractMonth('booked_date')
).values('month').annotate(
    total_commission_amount=Sum('commission_amount'),
    total_revenue_amount=Sum('total_amount'),
    total_booking=Count('id')
).order_by()