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 = 2005), value, name
Seems to never end Why is redesigning tables so difficult?! :-))
And fur
Stefan Schwarzer wrote:
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
Make sur
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_tota
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), yea
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 ... OR
Stefan Schwarzer wrote:
$curr_yr = -1
$cols = array();
while () {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) > 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols
$curr_yr = -1
$cols = array();
while () {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) > 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) > 0) { display_table_ro
Stefan Schwarzer wrote:
$curr_yr = -1
$cols = array();
while () {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) > 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols)
$curr_yr = -1
$cols = array();
while () {
if ($row['year'] != $curr_yr) {
if (sizeof($cols) > 0) { display_table_row($cols); }
$cols = array();
$curr_year = $row['year'];
}
$cols[] = $row['value'];
}
// handle possible last row of table
if (sizeof($cols) > 0) { display_table_row(
Stefan Schwarzer wrote:
Many, many thanks for that and the other advices from everybody. You're
great!
As I am neither expert in PHP, nor in Postgres, MySQL, Javascript, HTML
etc. etc., all I do in the forums is asking questions; and not "adding
value" by helping others (due to my limited kno
Many, many thanks for that and the other advices from everybody.
You're great!
As I am neither expert in PHP, nor in Postgres, MySQL, Javascript,
HTML etc. etc., all I do in the forums is asking questions; and not
"adding value" by helping others (due to my limited knowledge). All I
can d
Stefan Schwarzer wrote:
Thanks for the feedback and the suggestions.
A problem I have now when using the new design is the following:
As a result from my PostGres query I get something like this:
year|value |name
---
2001| 123
Thanks for the feedback and the suggestions.
A problem I have now when using the new design is the following:
As a result from my PostGres query I get something like this:
year|value |name
---
2001| 123 | Afghanistan
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/12/07 03:28, Stefan Schwarzer wrote:
[snip]
>
> The new design would be like this:
>
> id |year|value
> ---
> 1 | 1970| NULL
> 1 | 1971| 36
> 1
> 1 | 2005|
Stefan Schwarzer schrieb:
Hi there,
I learned in another posting that my table design - in a polite way -
"could be improved".
So, before doing any additional design errors, I would like to get
feedback, if possible.
I am dealing with some 500 tables for worldwide national statistics
(GDP
Stefan Schwarzer wrote:
Hi there,
I learned in another posting that my table design - in a polite way -
"could be improved".
So, before doing any additional design errors, I would like to get
feedback, if possible.
I am dealing with some 500 tables for worldwide national statistics
(GDP,
16 matches
Mail list logo
