Re: Aggregate sum() with false predicate

A workable solution if Red has 7.30+ but not for earlier versions. Art S. Kagel "Kim, Hyung Soo" wrote: > > 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 > >