From: | Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SELECT only those values of table B which have a value in table A |
Date: | 2010-02-25 08:34:19 |
Message-ID: | 36A078E0-3DFF-4C07-A4B7-5F1F4242CF3E@grid.unep.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi there,
I have tables with values for each country of the world.
Now, there is the possibility for the user to generate regional
(Europe, Africa...) or subregional (Western Europe, Central Europe...)
aggregations on-the-fly. In some cases, these aggregations need to be
calculated by using the population of each region too, as a
denominator, which looks like this:
SELECT
COALESCE(r.name, '''') AS name,
d.year_start AS year,
SUM(d.value * d_nom.value) / SUM(d_nom.value) AS value
FROM
pop_total_access_water AS d
RIGHT JOIN
countries_view AS c ON c.id = d.id_country
RIGHT JOIN
pop_total AS d_nom ON d_nom.id_country = d.id_country
RIGHT JOIN
subregions_view AS r ON r.id = c.sreg_id
WHERE
(d.year_start = 1990 ) AND
(d_nom.year_start = 1990 ) AND
d_nom.value <> 0
GROUP BY
r.name, d.year_start
ORDER BY 1,2
Now, I would like to use from table "d_nom" only these countries which
have a value in table "d" as well. What happens now is that, if my
table "d" has for example 2 countries with values and 2 countries
without values, but table d_nom has for all4 countries values, my end
result would be only 50% of the real value, which is wrong. I have to
multiply and to divide through the same amount of countries.
Could anyone give me a hint how this would work? Do I need to add a
subquery?
Thanks a lot!
Stef
From | Date | Subject | |
---|---|---|---|
Next Message | Wappler, Robert | 2010-02-25 08:47:20 | Re: helo |
Previous Message | Thomas Kellerer | 2010-02-25 08:30:47 | Re: Performance comparison |