Creating a sums value from a column on an SQLFORM.grid

[mostwanted]

Hi guys, I was wondering if i could create a sums column on an SQLFORM.grid so that every search that's made produces a sums value, something like this:

def payments():
    grid = SQLFORM.grid(db.payments, args=[db.payments])
    if grid.process().accepted:
        query=("#Search queries of the SQLFORMgrid")
        amount = db(query).select((db.payments.amount).sum().with_alias('total'))
    return locals()

Whats possible and whats not?

Regards;

[Jim S]

It looks like there is support for groupby in SQLFORM.grid which is what you'd need to build the proper SQL statement.

I haven't tried this with web2py.  If I were, I'd try adding a groupby clause and a list of fields that include your sum field.  Something like this (not tested)

payments_sum_field = db.payments.amount.sum()

SQLFORM.grid(db.payments, fields=[db.payments.field_1, db.payments.field_2, payments_sum_field], groupby=[db.payments.field_1, db.payments.field_2])

FWIW - I just submitted a PR for py4web to do exactly what you're talking about.  Looking at the code in web2py for groupby I see that I can improve what I'd submitted so far.

-Jim

[mostwanted]

I'm trying this code below & i'm getting <type 'exceptions.AttributeError'> 'Expression' object has no attribute 'tablename'    ERROR, whats wrong with my code? or What am i missing??

def payments():
    payments_sum_field = db.payments.amount.sum().with_alias('total')
    grid_table=SQLFORM.grid(db.payments, fields=[db.payments.client, db.payments.amount, db.payments.recorded,db.payments.recorded_on, payments_sum_field])
    return locals()

[Jim S]

I did a bunch of work in py4web yesterday getting this to work.  I was getting a similar error - I updated the py4web 'grid' code to fix it.  I've never used an Expression like this in web2py before so I can't say for sure that it is supported.

A couple other things I noticed.

1. You don't have a groupby specified on your .grid call.  With your code, if you get past your error, will only return 1 row with the total.

2. You're asking for the payments.amount field to be displayed as well as the payments_sum_field. It doesn't make sense to me that you'd want to display the individual amount along with the sum. I think you want something more like this:

def payments():
    payments_sum_field = db.payments.amount.sum().with_alias('total')

    grid_table=SQLFORM.grid(db.payments, fields=[db.payments.client, db.payments.recorded_on, payments_sum_field], groupby=[db.payments.client, db.payments.recorded_on])
    return locals()

With this you'll get one 1 record for each client for each day with their total payments received.

But, you'll still have to find how to fix your Expression object has no attribute tablename issue.

-Jim