try using groupby and sum
http://web2py.com/books/default/chapter/29/06#sum,-avg,-min,-max-and-len
On Friday, November 9, 2012 1:10:40 PM UTC-7, Rocco wrote:
>
> (I already post this question, but it was deleted in few seconds and no
> reason was mentioned. I try to repost it, removing formatting and external
> links... If something is wrong, please inform me :)
>
> I have defined this model:
>
> db.define_table('fruits_in_shop',
> Field('shop_name'),
> Field('species'),
> Field('number','integer'))
>
> With this kind of record inside:
> fruits_in_shop.id fruits_in_shop.shop_name fruits_in_shop.species
> fruits_in_shop.number
> 1 Mark's shop apple 32
> 2 Mark's shop pear 22
> 3 John's Market banana 22
> 4 John's Market apple 36
>
> I'm looking for a way to show the result as a "pivot table":
>
> Shop apple pear banana
> Mark's shop 32 22 0
> John's Market 32 0 22
>
> I often used a mysql trick to get this result, but there is a way to get
> this result using DAL?
>
> Otherwise I suppose to perform sequential queries and store the results in
> a dictionary.
> (fruits<-select distict fruit from table; for fruit in fruits:
> column<-select * from table where fruit=fruit;)
> In this case, there is a (easy) way to convert that dictionary into a
> gluon.sql.Rows object?
> Should I use its __init__ method?
>
--
