I am using mysql. Using my query in grid results into a table like the following:
3 records found Man_ID Man_Type 8 BC 8 BC 8 BC I want to add a groupby statement which will provide the following result: Man_ID Man_Type 8 BC Thanks, Matt On Saturday, August 3, 2013 12:40:31 PM UTC-7, Niphlod wrote: > > it may work on some backends (namely, SQLite), but that query doesn't > reeeeally make sense. What do you need as a result precisely ? > > On Saturday, August 3, 2013 9:25:44 PM UTC+2, Matt Grham wrote: >> >> But the following works perfectly: >> >> fields=['Man_ID','Man_Type'] >> sel=[db[str_tableName][field] for field in fields] >> >> rows = db(query).select(*sel,groupby=db[str_tableName].Man_ID) >> >> It does not work in sqlform.grid. Even in grid, it actually works but it >> also writes Unsupported Query on top of the table: >> >> Unsupported query >> Man_ID Man_Type >> 8 BC >> >> >> >> On Saturday, August 3, 2013 12:01:45 PM UTC-7, Niphlod wrote: >>> >>> here's the answer: there's no way you can do >>> >>> select man_id, man_type >>> from tablename >>> group by man_id >>> >>> and expecting it to return something meaningful. >>> >>> man_type needs to be either included in your groupby (in which case, it >>> equals a "distinct" type of query) or used as an aggregate (first, last, >>> count, etc etc etc) >>> >>> On Saturday, August 3, 2013 8:52:21 PM UTC+2, Matt Grham wrote: >>>> >>>> Fields to be shown: >>>> >>>> fields = ( db[str_tableName].Man_ID, >>>> db[str_tableName].Man_Type) >>>> >>>> On Saturday, August 3, 2013 11:32:18 AM UTC-7, Niphlod wrote: >>>>> >>>>> what are the "fields" you're requiring ? Do you know that any groupby >>>>> type of query can only include the group by column(s) and all the other >>>>> fields need to be an aggregate of the "basic" field (such as count, min, >>>>> max, etc) ? >>>>> >>>>> On Saturday, August 3, 2013 8:09:39 PM UTC+2, Matt Grham wrote: >>>>>> >>>>>> I have a table like: >>>>>> >>>>>> 3 records found >>>>>> Man_ID Man_Type >>>>>> 8 BC >>>>>> 8 BC >>>>>> 8 BC >>>>>> >>>>>> When I try to group by Man_ID, it does the grouping but it prints >>>>>> Unsupported Query on top of the table >>>>>> >>>>>> *Unsupported query >>>>>> Man_ID Man_Type >>>>>> 8 BC * >>>>>> >>>>>> I used the following statement: >>>>>> >>>>>> grid = SQLFORM.grid(query=query, fields=fields, >>>>>> headers=myheaders, >>>>>> create=False, deletable=False, editable=False, >>>>>> details=False, csv=False, >>>>>> maxtextlength=64, >>>>>> paginate=25, >>>>>> ui='jquery-ui', >>>>>> user_signature=False, >>>>>> searchable=searchable, >>>>>> sortable=sortable, >>>>>> groupby=db.mytable.Man_ID >>>>>> ) >>>>>> >>>>>> Can you help me here? >>>>>> >>>>>> Thanks, >>>>>> >>>>>> Matt >>>>>> >>>>> -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
