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