I have a table with the following columns: nombre , monto , fecha .
I want in a query to show the information grouped by name and month in the following way:
nombre1, suma(monto), marzo
nombre1, suma(monto), abril
nombre2, suma(monto), marzo
nombre2, suma(monto), mayo
How can I do it?
Literal, with "filters and annotations"
The example of the documentation is self-explanatory:
>>> from django.db.models import Count, Avg
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))
The month filter is a bit more complex, but you can see it this way:
from django.db import connection
from django.db.models import Sum
def index(request):
get_mes = connection.ops.date_trunc_sql('month', 'fecha')
modelos= Modelo.objects.filter(fecha__year=YEAR).extra({'mes': get_mes})
modelos_mes= modelos.values('mes').annotate(Sum('monto').order_by('mes')
What the example does is add a field month to the SQL query, on the one hand using the interface with the database and then use this field to make the fields.
I currently use connection.ops.date_trunc_sql without problems on a MySQL basis, but I'm not sure if it works on other engines.