| 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 |
<<attachment: smime.p7s>>
