[web2py] SQL avg()

[Richard Vézina]

Hello,

How I can make an avg() aggregate with web2py?

Richard

[Jim Steil]

I see sum, min, and max aggregates in the book at section 6.24.4, but no
avg. But, using sum and count (from 6.21.3) you could get there.

-Jim

[Richard Vézina]

Yeah, that's what I thought just after press send :)

Richard

On Thu, May 17, 2012 at 2:33 PM, Jim Steil <j...@qlf.com> wrote:

I see sum, min, and max aggregates in the book at section 6.24.4, but no avg.  But, using sum and count (from 6.21.3) you could get there.

   -Jim

[Richard Vézina]

print db().select(db.tab1.f2.sum()/db.tab1.f2.count(), groupby=db.tab1.f1)

Richard

[Richard Vézina]

Now I am searching a way to sum a list:reference type field with the proper representation of id...

Richard

[Jim Steil]

I just hacked a quick and dirty solution by inserting the following to dal.py at line 7370:

    def avg(self):
        return Expression(self.db, self.db._adapter.AGGREGATE, self, 'AVG', self.type)

Worked fine on my tests with MySQL, using it just like sum, but using avg instead.

    -Jim

[Richard Vézina]

Maybe open a ticket and propose a patch!!

Richard

[Jim Steil]

Done

http://code.google.com/p/web2py/issues/detail?id=803

    -Jim