Hello, Simon. Thank you very much! Your solution worked like a charm!!!
Best regards,
Felix.
El jueves, 28 de febrero de 2019, 0:09:35 (UTC-5), Simon Charette escribió:
>
> Hello Felix,
>
> month = ...
> year = ...
> Expenses.objects.filter(
> year=year,
> ).values(
> area_name=F('area__name'),
> element_name=F('element__name')
> ).annotate(
> year_sum=Sum('cost'),
> month_sum=Sum('cost', filter=Q(month=month)),
> )
>
> Should generate SQL along the following lines on PostgreSQL
>
> SELECT
> area.name AS area_name,
> element.name AS element_name,
> SUM(expenses.cost) AS year_sum,
> SUM(expenses.cost) FILTER (WHERE month = $month) month_sum
> FROM
> expenses
> INNER JOIN area ON (
> expenses.area_id = area.id
> )
> INNER JOIN element ON (
> expenses.element_id = element.id
> )
> WHERE expenses.year = $year
> GROUP BY area.name, element.name
> ORDER BY area.name, element.name
>
> Cheers,
> Simon
>
> Le mercredi 27 février 2019 14:04:21 UTC-5, Felix Lazaro Carbonell a
> écrit :
>>
>> Hi to everyone:
>>
>>
>>
>> Having models like these:
>>
>>
>>
>> class Area(models.Model):
>>
>> name = models.CharField(max_lentgh=30)
>>
>>
>>
>>
>>
>> class Element(models.Model):
>>
>> name = models.CharField(max_lentgh=30)
>>
>>
>>
>>
>>
>> class Expenses(models.Model):
>>
>> area = models.ForeignKey(Area, on_delete=models.CASCADE )
>>
>> element = models.ForeignKey(Element, on_delete=models.CASCADE )
>>
>> year = models.SmallIntegerField()
>>
>> month = models.SmallIntegerField()
>>
>> cost = model.DecimalField(max_digits=10, decimal_places=2, default=0)
>>
>>
>>
>>
>>
>> I would like to group by area, then by element, and then get sum by month
>> and accumulated expenses for that element in the year. how could that be
>> done using Django ORM?
>>
>>
>>
>> Something to show like this
>>
>>
>>
>> Expenses in
>> current
>>
>> Area Element Month Year
>>
>> Operations Cell phone 325.65 712.40
>>
>> Operations Office consumibles 451.00 1028.56
>>
>> Main office Cell phone 148.89 284.41
>>
>> Main office Office consumibles 650.00 1300.00
>>
>>
>>
>> Thanks in advance,
>>
>> Felix.
>>
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/3f22fd8e-b1ea-4e68-86b4-f042123ab43e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
