Re: Database/Table Design for Global Country Statistics

>> Uiuiui.... and it gets even worse... I want to implement the
>> possibility to calculate on-the-fly the per Capita values for the
>> selected data set. With the "old" table design it would be
>> something like this:
>>       SELECT (fish_catch.y_1970 / pop_total.y_1970),
>> (fish_catch.y_1971 / pop_total.y_1971) FROM .....
>
> Or, if the fish + population data are in different tables:
>
> SELECT
>   f.year,
>   f.country,
>   f.fish_catch
>   p.pop_total
>   (f.fish_catch / p.pop_total) AS fish_per_capita
> FROM
>   fish_figures f
> JOIN
>   popn_figures p
> USING (year, country)
> ORDER BY f.year, f.country;
>

Muchos gracias, Mr. Postgres! I am really touched by your help!

Just for the completeness, I attach the final working SQL query:

SELECT
   f.year,
   f.id,
   c.name,
   (f.value / p.value) AS per_capita
FROM
   fish_catch AS f
JOIN
   pop_total AS p
USING
    (year, id)
INNER JOIN
    countries AS c ON f.id = c.id
ORDER BY
    year