Re: Aggregate sum() with false predicate

select sum(nvl(col1, 0)) from sumsamp where nm = "brown"; or select count(*) from sumsamp where nm="brown"; -- ---------------------------------------- Kim, Hyung Soo eIST Co. Senior Developer & DBA Tel : 6283-2108 Mailto:dankoon@ist.co.kr Mailto:dankoon@chollian.net ---------------------------------------- Red Valsen <red_valsen@yahoo.com>''('') 'Ʒ' ''''''' news:37E7A2D9.F894F8F8@yahoo.com'' 'Խ'''''''''. > 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 >