This part of the web2py book might help: http://www.web2py.com/book/default/chapter/06#sum-avg-min-max-and-len
On Monday, April 1, 2019 at 6:41:51 PM UTC-4, João Matos wrote: > > Hello, > > I have this SELECT in SQLite > > result = db.executesql( > "SELECT auth_user.id, auth_user.username, " > "auth_user.canceled_on, " > "auth_user.canceled_by, " > "SUM(auth_group.role = 'Operador') operador, " > "SUM(auth_group.role = 'Verificador') verificador, " > "SUM(auth_group.role = 'Supervisor') supervisor " > "FROM auth_user " > "LEFT JOIN auth_membership ON auth_user.id = > auth_membership.user_id " > "LEFT JOIN auth_group ON auth_group.id = > auth_membership.group_id " > "GROUP BY username ORDER BY username" > ) > > and would like to convert it to web2py's DAL. > I made this > > query = db().select( > db.auth_user.id, > db.auth_user.username, > db.auth_user.canceled_on, > db.auth_user.canceled_by, > left=( > db.auth_membership.on(db.auth_user.id == db.auth_membership. > user_id), > db.auth_group.on(db.auth_group.id == db.auth_membership.group_id), > ), > groupby=db.auth_user.username, > orderby=db.auth_user.username, > ) > > but I can't find any way to integrate the sums. > > Does anyone know how to do it? > > Thanks, > > JM > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- 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/d/optout.
