Django ORM how to Round an Avg result

django orm model rounding average
Ami picture Ami · Dec 10, 2012 · Viewed 10.5k times · Source

I have a model in which I use Django ORM to extract Avg of values from the table. I want to Round that Avg value, how do I do this?

See below I am extracting Avg price from Prices model grouped by date in format YYYY-MM, I want to automatically extract the average values rounded to the closest number.

rs = Prices.objects.all.extra(select={
    'for_date': 'CONCAT(CONCAT(extract( YEAR from for_date ), "-"),
        LPAD(extract(MONTH from for_date), 2, "00"))'
    }).values('for_date').annotate(price=Avg('price')).order_by('-for_date')

Answer

mrts picture mrts · Jan 5, 2016

Use Func() expressions.

Here's an example using the Book model from Django Aggregation topic guide to round to two decimal places in SQLite:

class Round(Func):
    function = 'ROUND'
    template='%(function)s(%(expressions)s, 2)'

Book.objects.all().aggregate(Round(Avg('price')))

This allows the round function to be parameterised (from @RichardZschech's answer):

class Round(Func):
  function = 'ROUND'
  arity = 2

Book.objects.all().aggregate(Round(Avg('price'), 2))

Related questions

How can I iterate over ManyToManyField?

A simple question and yet I can't find an answer for it. I have a model with a ManyToMany field: class Stuff(models.Model): things = models.ManyToManyField(Thing) then in a different function I want to do this: myStuff = Stuff.…

Read More
select and update database record with a single queryset

How do I run an update and select statements on the same queryset rather than having to do two queries: - one to select the object - and one to update the object The equivalent in SQL would be something …

Read More
Why does django's prefetch_related() only work with all() and not filter()?

suppose I have this model: class PhotoAlbum(models.Model): title = models.CharField(max_length=128) author = models.CharField(max_length=128) class Photo(models.Model): album = models.ForeignKey('PhotoAlbum') format = models.IntegerField() Now if I want to look at a subset of …

Read More