Re: Help with aggregates needed

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+unsubscribe@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/36e57d46-3577-4afd-84cc-63ea31ee3a96%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.