ERRATA: (groupby not orderby, moreover indentation is messed up)
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(),
groupby=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.
On May 20, 11:12 am, mdipierro <[email protected]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
