On Wed, Oct 17, 2007 at 08:29:58AM +0200, Stefan Schwarzer wrote:
> 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
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_2
On Oct 16, 2007, at 8:09 , Stefan Schwarzer wrote:
That is, for one country it can be 1990, for another 1992, for a
third 1995. As the actual year is not important (and not always
defined), one tries to use this kind of common describer: various.
When you don't have the year, put them in a
I suggest using two *date* (or possibly integer) columns for each
row and consider each row an year interval (in the mathematical
sense, not to be confused with SQL intervals, which are actually
durations). Depending on the interval representation you choose
(closed-open or closed-closed),
On Oct 15, 2007, at 10:50 , brian wrote:
Adjust your data so that table agri_area has a row for each year?
I can imagine that some of his raw data is a multi-year aggregate, so
it may be difficult for him to do this.
Otherwise, i think you'd need to parse your year column by
expanding an
On Mon, Oct 15, 2007 at 02:08:24PM +0200, Stefan Schwarzer wrote:
> 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?
I'
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 O
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_c
Stefan Schwarzer skrev:
> Hi there,
>
> I need to calculate per Capita data on-the-fly. My table for a given
> variable looks like this:
>
> year|value |id_country
> ---
> 2001| 123 | 1
> 2002| 125 |
