Aggregate sum() with false predicate

Why is the behavior of the sum() function identical for cases in which the predicate is false and in which summed columns are null? For example, if I create a table: create table sumsamp( col1 int, nm char(8); And populate it: insert into sumsamp values (1, "jones"); insert into sumsamp values (1, "jones"); insert into sumsamp(nm) values ("brown"); insert into sumsamp(nm) values ("brown"); And run these queries: select sum(col1) from sumsamp where nm = "brown"; select sum(col1) from sumsamp where nm = "nothere"; I get the same result, namely: (sum) 1 row retrieved In other words, a null is returned in both cases. Why is this? Why doesn't the query testing for "nothere" return "no rows found"? Is there a workaround? Or am I missing something? tia

Red Valsen wrote: > > Why is the behavior of the sum() function identical for cases in which > the predicate is false and in which summed columns are null? For > example, if I create a table: > > create table sumsamp( > col1 int, > nm char(8);> > And populate it: > > insert into sumsamp values (1, "jones"); > insert into sumsamp values (1, "jones"); > insert into sumsamp(nm) values ("brown"); > insert into sumsamp(nm) values ("brown");> > And run these queries: > > select sum(col1) from sumsamp where nm = "brown"; > select sum(col1) from sumsamp where nm = "nothere"; > > I get the same result, namely: > > (sum) > > 1 row retrieved > > In other words, a null is returned in both cases. Why is this? Why > doesn't the query testing for "nothere" return "no rows found"? Is > there a workaround? Or am I missing something? You are missing something. All aggregates ALWAYS return a result, it is just that sometimes the result is NULL. The correct result of the SUM() or either no rows, or of a set of rows containing NULLS in the SUMed column, is NULL. The problem here is a Relational classic. According to Codd and Date there should be two distinct NULL values: DNI and DNA, data not available (DNA) and data not in (DNI). You first SELECT, returning the sum of two rows containing NULLS which should probably be DNIs should rightly be a DNI on the assumption that if the row exists then the data for the 'l' column will show up eventually and is just "not in" yet. Of course if those rows contained a DNA instead of a DNI then the SUM() might be DNA also. The latter could correspond to a DNA as there are no rows "available" that match the filter criteria. Since there are no relational databases that define more than one NULL value such anomolies as this, where one cannot analyse the result to determine the nature of the data, abound. This is but one example. To determine the meaning of the NULL that SUM returns you will have to SELECT COUNT(*) WHERE nm = "brown";and SELECT COUNT(*) WHERE nm = "nothere" Art S. Kagel