Re: Calculation of per Capita on-the-fly - problems with SQL syntax

>> SELECT DISTINCT
>> ( CASE WHEN d.year=2003 AND pt.year=2003 AND pt.value <> '0' AND
>> pt.value IS NOT NULL THEN d.value / pt.value ELSE NULL END ) AS
>> y_2003,
>> ( CASE WHEN d.year=2002 AND pt.year=2002 AND pt.value <> '0' AND
>> pt.value IS NOT NULL THEN d.value / pt.value ELSE NULL END ) AS
>> y_2002,
>> c.name
>> FROM
>> public_multiple_tables.agri_area AS d
>> LEFT JOIN
>> public_multiple_tables.pop_total AS pt ON pt.id_country =
>> d.id_country
>> LEFT JOIN
>> countries_view AS c ON c.id = d.id_country
>> ORDER BY
>> name ASC
>>
>>
>> What am I doing wrong? Thanks for any advice,
>
> You are trying to do the join on the year in the SELECT expression.
> Also, you are trying to do the formatting into year-columns in your
> query. You are left joining to tables in which there should always
> be a
> mathing row (I assume).
>
> This should give you the same data out in a different format. Note
> that
> most of the NULL values will be excluded from this result.
>
> SELECT cname, year, d.value/pt.value
> FROM
> public_multiple_tables.agri_area AS d
> INNER JOIN
> public_multiple_tables.pop_total AS pt ON pt.id_country =
> d.id_country AND pt.year = d.year
> INNER JOIN
> countries_view AS c ON c.id = d.id_country
> WHERE d.year in (2002,2003,2004)
> AND pt.value <> 0
> ORDER by c.name, year;

Hmmm.... Actually, my intention was to get a more "excel" like
output, that is the formatting into year-columns. This eases a lot
the PHP/HTML display/loop. Otherwise I would have to start to do some
joggling inside PHP to get it that way....