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

Stefan Schwarzer wrote:
>> 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;
>
>
> Ah... Thanks a lot for that.
>
> Just one thing: As my year columns can have as well values like
> "1970-75", they are not integers, but text fields. Thus, the "IN"
> parameter in the "WHERE" clause doesn't work. Do you have any other
> idea how ti could work?
>

Adjust your data so that table agri_area has a row for each year?

Otherwise, i think you'd need to parse your year column by expanding any
entries with a dash into a range. But i'm not sure that you could then
compare a range against a list (WHERE ... IN ... )

Probably best done in a function.

brian