Re: SQL query with IFs (?) to "Eliminate" NULL Values

On 9/5/07, Stefan Schwarzer <stefan.schwarzer@grid.unep.ch> wrote:
> Hi there,
>
> I want to calculate per Capita values on-the-fly, taking for example
> the "Total GDP" data set and divide it by "Total Population". Now,
> each of these data sets have a couple of "0" or "-9999" values (the
> latter being the indicator for : "no data available").

Why not use real NULLs there.  If you do avg over a set of data with
nulls, the nulls don't count.  i.e.:

create table test (a int);
insert into test values (1);
insert into test values (2);
insert into test values (NULL);
select avg (a) from test;
        avg
--------------------
 1.5000000000000000

select count (a) from test;
 count
-------
     2

NULL acts that way automatically.  You're jumping through hoops to
implement what has already been implemented.