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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
