This help. web2py has it:
db().select(db.auth_user.ALL, db.log.points.sum().coalesce_zero(),
left=db.log.on(db.auth_user.id == db.log.user),
groupby=db.auth_user.id)
I also added to trunk (just know), a more general:
db().select(db.auth_user.ALL, db.log.points.sum().coalesce(0),
left=db.log.on(db.auth_user.id == db.log.user),
groupby=db.auth_user.id)
On Aug 7, 7:57 pm, Cliff <cjk...@gmail.com> wrote:
> Google for COALESCE in your database engine of choice. You can use it
> to force (coalesce) null values to zero or 'None.'
>
> I don't know if the DAL knows about COALESCE, though. You may have to
> make some native SQL.
>
> On Aug 6, 6:54 pm, Tim Korb <jtk...@gmail.com> wrote:
>
>
>
>
>
>
>
> > I have a table ("log") of points awarded to students for various activities.
> > I want to produce a summary of total points per student. This query
> > works...
>
> > db().select(db.auth_user.ALL, db.log.points.sum(),
> > left=db.log.on(db.auth_user.id == db.log.user), groupby=db.auth_user.id)
>
> > but does not include students with no entries in the log table. I'd like
> > them to have a point total of 0. I suspect the problem is with
> > db.log.points.sum() failing on null values, but I don't know how to include
> > them.
>
> > Suggestions?
