Re: Find "smallest common year"

[Stefan Schwarzer <stefan.schwarzer@xxxxxxxxxxxx>]

Sorry,

I forgot to mention my table design, which is like this:

     name             2001    2002   2003   2004   2005

-----------------------------------------------------------------

Afghanistan    ....

Albania            ....

(Yep, I know, bad table design.... :-)). I tried to change it to the more common "id | year | value" format, but there were too many SQL problems afterwards for the different queries/calculations we need to have....)

So, the mentioned SQL unfortunately doesn't work in that case....

Stef

On Sep 27, 2007, at 1:52 PM, William Leite Araújo wrote:

    If I understood your question, maybe it's you want:
  
   SELECT min(<desired data>) FROM table1 JOIN table2 ON (table1.data_field = table2.data_field);

2007/9/27, Stefan Schwarzer <stefan.schwarzer@xxxxxxxxxxxx >:

Hi there,

my aim is to plot a line graph for a single country but for two or

three variables. Now, one could use two different y-axis (if there

are two data sets with different units); but it's not really the

right way to analyse data.

One should rather normalize them to a base year to enhance

comparisons. To achieve this, one would set the values for a specific

year to 1 (or 100) and put the other values of all variables in

relation to it.

Thus, taking two variables, say,

Fish Catch 

- in Thousand Metric Tons

- data available for 1975 to 2005

and

Total Population

- in Million People

- data available for 1960 to 2005

Country is Kenya.

The "smallest common year" would be 1975. One needs thus to set the

values for that year to 1 (or 100), and calculate the other values of

the variables in respect to the needed factor.

Now the question is how to find that "smallest common year"

automatically, via SQL or PHP?

Can anyone give me a hint? Thanks for any help!

Stef

 ____________________________________________________________________

  

   Stefan Schwarzer

  

  Lean Back and Relax - Enjoy some Nature Photography :  

  http://photoblog.la-famille-schwarzer.de

   

   Appetite for Global Dat a? UNEP GEP Data Portal:   

  http://geodata.grid.unep.ch

   ____________________________________________________________________

--
William Leite Araújo
Estudante de paternidade - 24a semana