How to use SUM()

[minh]

I couldn't find much documentation on sum() and I'm not sure how to
use it.

If I have something like

define_table('some_table',
Field('amount', 'integer')
...

I try db(...).select(db.some_table.amount.sum())

When I print it out, I get something like:

SUM(some_table.amount)
300

How do I get it to print just the number?

Thanks.

[mdipierro]

rows=db(...).select(db.some_table.amount.sum())
print rows.first()[db.some_table.amount.sum()]

[Russell]

The number is stored in a dictionary called '_extra'. The top part of
what you are getting is the dictionary key. Do something like this to
get just the number...

row = db(...).select(db.some_table.amount.sum())[0]
answer = row.'_extra['SUM(some_table.amount) ']

See pg 169 of the book.

[Thadeus Burgess]

Is it just me or is this archaic?

-Thadeus

> --
> You received this message because you are subscribed to the Google Groups "web2py-users" group.
> To post to this group, send email to web...@googlegroups.com.
> To unsubscribe from this group, send email to web2py+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
>
>

[mr.freeze]

It's not just you.

On Mar 9, 8:23 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Is it just me or is this archaic?
>
> -Thadeus
>

[mdipierro]

s=db.some_table.amount.sum()
row = db(...).select(s).first()
answer = row[s]

On Mar 9, 8:23 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:

> Is it just me or is this archaic?
>
> -Thadeus
>

[Thadeus Burgess]

Still doesn't seem natural.

db(...).select(....).sum(db.table.field) seems much more natural.

rows = db(db.table.id > 0).select(orderby=~db.table.datetimestamp)

total_hours = rows.sum(db.table.hours_worked)
avg_hours = rows.avg(db.table.hours_worked)

Would the actual implementation of this be difficult ?

-Thadeus

[mr.freeze]

Ah, this makes more sense now.

[mdipierro]

I disagree. In the case you are proposing, the computation would be
done web2py site since you are fetching all rows.
In the current implementation you can do

s=(db.some_table.amount.sum()
+db.some_table.amount.min())*(db.some_table.amount.max()+3)

row = db(...).select(s).first()
answer = row[s]

and it still works. The computation is done by the database without
fetching data. It is very general.

On Mar 9, 9:08 pm, Thadeus Burgess <thade...@thadeusb.com> wrote:
> Still doesn't seem natural.
>
> db(...).select(....).sum(db.table.field) seems much more natural.
>
> rows = db(db.table.id > 0).select(orderby=~db.table.datetimestamp)
>
> total_hours = rows.sum(db.table.hours_worked)
> avg_hours = rows.avg(db.table.hours_worked)
>
> Would the actual implementation of this be difficult ?
>
> -Thadeus
>

[Thadeus Burgess]

You raise a good point. If the calculations are done database side
then I suppose it is worth the difficulty in syntax.

I just wish the syntax was more natural.

-Thadeus