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