As others have noted, the query *can* be written. But it appears
to me
that you are struggling against your table layout.
The current schema he has is commonly called EAV (entity-attribute-
value) and is generally frowned upon. Now, in his particular case
it may be justified if the "value" column values are actually all
of the same type, such as currency amounts for each category. If
this is the case, I suggest renaming the column to be more
descriptive of what is actually stored: likewise the id_variable
column.
Hmmm.... I am somewhat surprised to here so. After being told in this
forum how "bad" my old table design was, I changed it to the current
(which is less than alpha). Perhaps to summarize: Having 500
statistical global national variables for about 240 countries/
territories. Need to do regional aggregations, per Capita
calculations and some completeness computations on-the-fly.
The design was a table like this for each variable:
id_country | 1970 | 1971 | ... | 2004 |
2005
-------------------------------------------------------------------
1 | NULL | 36 | ... |
42 | 45
2 ......
The new like this:
id_variable | year | value | id_country
---------------------------------------
1 | 2001 | 123 | 1
1 | 2002 | 125 | 1
1 | 2003 | 128 | 1
1 | 2004 | 132 | 1
1 | 2005 | 135 | 1
1 | 2001 | 412 | 2
1 | 2002 | 429 | 2
1 | 2003 | 456 | 2
1 | 2004 | 465 | 2
1 | 2005 | 477 | 2
....
2 | 1980 | 83 | 1
2 | 1981 | 89 | 1
....
I thought (and did ask) about the possibility to put nevertheless -
with the new table design - the variables into different tables, but
nobody really got my on a track for that. So I thought the most
"common" way would be to have this central table.
But I am at a stage where I still can change - and would very much
like to get your advice.
Thanks a lot!
Stef
____________________________________________________________________
Stefan Schwarzer
Lean Back and Relax - Enjoy some Nature Photography:
http://photoblog.la-famille-schwarzer.de
Appetite for Global Data? UNEP GEO Data Portal:
http://geodata.grid.unep.ch
____________________________________________________________________
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
