Обсуждение: SQL query with IFs (?) to "Eliminate" NULL Values

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").

Until now I have it working like this:

     SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS
y_2004, countries_view.name AS name
     FROM pop_total, countries_view
     LEFT JOIN tpes_total ON tpes_total.id = countries_view.id
     WHERE pop_total.y_2004<> '0' AND pop_total.y_2004<> '-9999' AND
tpes_total.y_2004 <> '-9999' AND countries_view.id = pop_total.id
     ORDER BY name ASC

But then it eliminates the countries having these "0" or "-9999" values.

In principal I still would like to have them in my final $result, and
then via PHP display them in grey (or with "x" or something like that).

So, I guess I'd need some kind of IF statement to do the calculation
only with "valuable" numbers and pass the others as they are.

But I have no idea how this would work.

I would me most grateful if someone could give me a hint how to
achieve that.

Thanks a lot.

Stef

On Wed, Sep 05, 2007 at 11:15:43AM +0200, Stefan Schwarzer wrote:
>     SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS
> y_2004, countries_view.name AS name
>     FROM pop_total, countries_view
>     LEFT JOIN tpes_total ON tpes_total.id = countries_view.id
>     WHERE pop_total.y_2004<> '0' AND pop_total.y_2004<> '-9999' AND
> tpes_total.y_2004 <> '-9999' AND countries_view.id = pop_total.id
>     ORDER BY name ASC
> So, I guess I'd need some kind of IF statement to do the calculation
> only with "valuable" numbers and pass the others as they are.
> But I have no idea how this would work.

SELECT DISTINCT
    (
        CASE
            WHEN pop_total.y_2004<> '-9999' AND tpes_total.y_2004 <> '-9999'
                then tpes_total.y_2004 / pop_total.y_2004
            ELSE '-9999'
        END
    ) AS  y_2004,
    countries_view.name AS name

should work.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

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.

Stefan Schwarzer skrev:
> 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").
>
> Until now I have it working like this:
>
>     SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS y_2004,
> countries_view.name AS name
>     FROM pop_total, countries_view
>     LEFT JOIN tpes_total ON tpes_total.id = countries_view.id
>     WHERE pop_total.y_2004<> '0' AND pop_total.y_2004<> '-9999' AND
> tpes_total.y_2004 <> '-9999' AND countries_view.id = pop_total.id
>     ORDER BY name ASC
>
> But then it eliminates the countries having these "0" or "-9999" values.
>
> In principal I still would like to have them in my final $result, and
> then via PHP display them in grey (or with "x" or something like that).
>
> So, I guess I'd need some kind of IF statement to do the calculation
> only with "valuable" numbers and pass the others as they are.
>
> But I have no idea how this would work.

Another idea -using a left join with additional criteria. I agree with
the suggestion to use real NULLS to signify mising data - but you still
have to work around the issue with population=0 though

   SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS y_2004,
countries_view.name AS name
    FROM countries_view LEFT JOIN pop_total ON countries_view.id =
pop_total.id AND pop_total.y_2004<> '0' AND pop_total.y_2004<> '-9999'
    LEFT JOIN tpes_total ON tpes_total.id = countries_view.id AND
tpes_total.y_2004 <> '-9999' AND countries_view.id = pop_total.id
    ORDER BY name ASC

(As a question of style, I would suggest never to mix ANSI-joins with
commaseparated tables lists. Use ANSI-joins. They are good for you.)

Nis