Help with aggregates needed
Felix Lazaro Carbonell
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.
Simon Charette
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
