Thread: 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