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

On 9/5/07, Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)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.