This should do it
rows=db(db.country.id==db.sales.country).select
(db.sales.product,db.sales.date,db.country.name,db.sales.amount.sum
(),orderby=db.sales.product|db.product.date,db.product.country)
for row in rows:
print row.sales.product, row.sales.date, row.country.name,
row._extra[db.sales.amount.sum()]
BUT 'date' is not a field name in SQL. make it 'date_of_sale' and the
type of that field should be 'date' not string as the default.
Massimo
On May 20, 10:59 am, Hans <[email protected]>
wrote:
> I need a list of
> sales.product, sales.date, continent.name, sales.amount.sum()
> with just one summed row per sales.product, sales.date,
> continent.name !
> basically a sales total per product, day and continent.
>
> something like
> Banana 20090520 Europe 123
> Apple 20090520 Europe 234
> Apple 20090520 Asia 456
>
> db.define_table('sales',
> SQLField('product'),
> SQLField('date'),
> SQLField('country','reference country',requires=IS_IN_DB
> (db,'country.id','%(name)s')),
> SQLField('amount','integer'))
>
> db.define_table('country',
> SQLField('name'),
> SQLField('continent','reference continent',requires=IS_IN_DB
> (db,'continent.id','%(name)s')))
>
> db.define_table('continent',
> SQLField('name'))
>
> how should the web2py query/select look like?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
