Re: Database/Table Design for Global Country Statistics

Stefan Schwarzer wrote:
>
>> Umm - not sure what you're after. What's wrong with one of:
>> SELECT ... ORDER BY year, value
>> SELECT ... ORDER BY value, year
>>
>> Or did you want a particular year pulled out of the general list, in
>> which case try something like:
>> SELECT ... ORDER BY (year = 1970), year, value
>> SELECT ... ORDER BY (year <> 1970), year, value
>> This works because booleans are considered sortable too.
>
> 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 .....

SELECT
year,
country,
fish_catch,
pop_total,
(fish_catch / pop_total) AS fish_per_capita
FROM my_table
ORDER BY year,country

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;

HTH
--
Richard Huxton
Archonet Ltd