Thread: null answer - how?
select coalesce(sum(deltafromoctets),0) from trans,stats where stats_id=stats.id and (timeslicet < '1:05' OR timeslicet > '6:05') returns a number (9188191930), whereas select coalesce(sum(deltafromoctets),0) from trans,stats where stats_id=stats.id and (timeslicet < '1:05' OR timeslicet > '6:05') and timesliced < '01-May-2001' prints 0 (or null without the coalesce) as does select coalesce(sum(deltafromoctets),0) from trans,stats where stats_id=stats.id and timesliced < '01-May-2001' How can this be? select * from stats where timesliced is null or timeslicet is null select * from trans where deltafromoctets is null both retun 0 rows. stats_id and stats.id are "not null" timesliced date, timeslicet time, deltrafromoctets bigint. Any hints? Cheers, Patrick
On Wed, 19 Sep 2001, Patrick Welche wrote: > select coalesce(sum(deltafromoctets),0) > from trans,stats > where stats_id=stats.id > and (timeslicet < '1:05' OR timeslicet > '6:05') > > returns a number (9188191930), whereas > > select coalesce(sum(deltafromoctets),0) > from trans,stats > where stats_id=stats.id > and (timeslicet < '1:05' OR timeslicet > '6:05') > and timesliced < '01-May-2001' > > prints 0 (or null without the coalesce) as does > > select coalesce(sum(deltafromoctets),0) > from trans,stats > where stats_id=stats.id > and timesliced < '01-May-2001' > > How can this be? > > select * from stats where timesliced is null or timeslicet is null > select * from trans where deltafromoctets is null > > both retun 0 rows. Are there no rows with timesliced<'01-May-2001'? I think in that case sum() gives you one row with NULL.
On Wed, Sep 19, 2001 at 11:34:56AM -0700, Stephan Szabo wrote: > > Are there no rows with timesliced<'01-May-2001'? I think in that > case sum() gives you one row with NULL. Thank you for the clue! *slaps forehead* transatlantic=# select min(stats_id),max(stats_id) from trans; min | max ------+------- 5415 | 12532 (1 row) transatlantic=# select min(id),max(id) from stats where timesliced<'01-May-2001'; min | max -----+------ 10 | 2345 (1 row) Cheers, Patrick