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