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