21

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')
borges
  • 3,627
  • 5
  • 29
  • 44
Ami
  • 495
  • 1
  • 4
  • 13
  • Have you worked this out by chance? – Nicholas Hamilton Apr 08 '14 at 13:02
  • 1
    Update: a similar function to the given answers was added in `Django v2.2` under the name [`Round`](https://docs.djangoproject.com/en/2.2/ref/models/database-functions/#django.db.models.functions.Round) – Ralf Mar 30 '20 at 11:34

5 Answers5

46

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))
Antoine Pinsard
  • 33,148
  • 8
  • 67
  • 87
mrts
  • 16,697
  • 8
  • 89
  • 72
  • 2
    Depending on the complexity of your annotation or aggregation, you may need to assign an alias. e.g. `Book.objects.all().aggregate(rounded_avg_price=Round(Avg('price')))` – Duncan Jul 20 '16 at 15:21
  • @Blairg23, thanks for the edits! But don't you think that the bold and large headers make the text a bit too loud? In my experience using bold and large headers is rare in StackOverflow and they are mostly used to structure longer answers. – mrts Oct 11 '18 at 10:00
  • @mrts I was just preparing it to be a longer answer! ;) But also, I never think being more explicit is a bad thing :) – Blairg23 Oct 15 '18 at 06:32
  • @Blairg23, right - but does the current version look good to you? – mrts Oct 16 '18 at 14:11
  • @mrts It looks fine, but I always prefer to make the statements stand out more from the code examples, so I boldify or headerify any text that I consider the "opening line" for a bit of code examples. Personal preference, I guess. – Blairg23 Oct 17 '18 at 02:21
  • @mrts Also, shouldn't you keep the changes I made in terms of the latest 1.11 docs and the 2.1 docs as well? – Blairg23 Oct 17 '18 at 02:22
  • 1
    @Blairg23, thanks, all good then! Regarding 1.11 docs - I think it is more succinct to have links only to 2.1 docs. The relevant part of the API is the same and we can assume that people are intelligent enough to switch to 1.11 if they need to. – mrts Oct 17 '18 at 07:48
  • I got: "TypeError: Complex aggregates require an alias" with this solution and can not find any way around it :( – Macilias Jun 08 '22 at 12:42
22

Building on previous answers, I've come to this solution to make it work for PostgreSQL:

from django.db.models import Func

class Round2(Func):
    function = "ROUND"
    template = "%(function)s(%(expressions)s::numeric, 2)"

# Then use it as ,e.g.:
# queryset.annotate(ag_roi=Round2("roi"))

# qs.aggregate(ag_sold_pct=Round2(Sum("sold_uts") / (1.0 * Sum("total_uts"))) * 100
msonsona
  • 1,306
  • 14
  • 20
15

Improving on @mrts answer.

This allows the round function to be parameterised:

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

Book.objects.all().aggregate(Round(Avg('price'), 2))
Richard Zschech
  • 624
  • 5
  • 10
  • 1
    This doesn't work in PostgreSQL. Any way to use arity with psql? – rain01 Oct 09 '18 at 01:12
  • I updated your answer, but I also added it to the answer with more votes and will flag that answer to be the accepted answer. – Blairg23 Oct 09 '18 at 19:05
  • 1
    I'm getting this ```django.db.utils.ProgrammingError: function round(double precision, integer) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts.``` msonsona's solution worked for me but can't use arity with that. – rain01 Oct 09 '18 at 19:42
  • @rain01 can you try rounding to `2.0` rather than `2`. See https://stackoverflow.com/questions/13113096/how-to-round-an-average-to-2-decimal-places-in-postgresql – Richard Zschech Oct 10 '18 at 01:14
6

Django has the Round function. For more detail please see the documentation

M.Void
  • 2,764
  • 2
  • 29
  • 44
  • 3
    This function is available since Django >= 2.2. In the developer Version from 3.3 they add the precision argument. – Tobit Jun 30 '21 at 08:42
5

I needed to have both PostgreSQL and SQLite support, but also keep the ability to specify the number of digit to keep.

Build on previous answers :

class Round(Func):
    function = 'ROUND'
    arity = 2
    # Only works as the arity is 2
    arg_joiner = '::numeric, '

    def as_sqlite(self, compiler, connection, **extra_context):
        return super().as_sqlite(compiler, connection, arg_joiner=", ", **extra_context)

# Then one can use it as:
# queryset.annotate(avg_val=Round(AVG("val"), 6))

I would have prefered something cleaner like

if SQLITE:
    arg_joiner=", "
elif PGSQL:
    arg_joiner = '::numeric, '
else raise NotImplemented()

but did not find how, feel free to improve !

Bryan Brancotte
  • 311
  • 3
  • 7