Re: SQL query with IFs (?) to "Eliminate" NULL Values - Mailing list pgsql-general

From Scott Marlowe
Subject Re: SQL query with IFs (?) to "Eliminate" NULL Values
Date
Msg-id dcc563d10709050747s39c78fccp8dca67a049b3ac3b@mail.gmail.com
Whole thread Raw
In response to SQL query with IFs (?) to "Eliminate" NULL Values  (Stefan Schwarzer <stefan.schwarzer@grid.unep.ch>)
List pgsql-general
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.

pgsql-general by date:

Previous
From: "Rodrigo De León"
Date:
Subject: Re: Partition Reindexing
Next
From: "Scott Marlowe"
Date:
Subject: Re: SQL for Deleting all duplicate entries