I have two tables like this:
Table A
id | sum_value | city |
---|---|---|
1 | 14 | Paris |
1 | 4 | Paris |
2 | 10 | Berlin |
3 | 68 | Milan |
4 | 51 | London |
3 | 2 | Milan |
Table B
id | city | geom |
---|---|---|
1 | Paris | MULTIPOLYGON(XXX) |
2 | Berlin | MULTIPOLYGON(XXX) |
3 | Milan | MULTIPOLYGON(XXX) |
4 | London | MULTIPOLYGON(XXX) |
I would like to make a selection of a sum of a field (sum_value
) for all cities and add the city geometry to it. I use a group by to have the cities as unique values with the sum. This is possible with the following code:
SELECT SUM(tablea.sum_value), tableb.city
FROM tablea, tableb
WHERE tablea.id = tableb.id
GROUP BY tableb.city
So I get something like this:
sum | city |
---|---|
18 | Paris |
10 | Berlin |
70 | Milan |
51 | London |
To add the city geometry I tried following but I does not give the expected result. The city values are not unique anymore and the SUM does not work.
SELECT SUM(tablea.sum_value), tableb.city, tableb.geom
FROM tablea, tableb
WHERE tablea.id = tableb.id
GROUP BY tableb.city, tableb.geom
So I get this:
sum_value | city | geom |
---|---|---|
14 | Paris | MULTIPOLYGON(XXX) |
4 | Paris | MULTIPOLYGON(XXX) |
10 | Berlin | MULTIPOLYGON(XXX) |
68 | Milan | MULTIPOLYGON(XXX) |
51 | London | MULTIPOLYGON(XXX) |
2 | Milan | MULTIPOLYGON(XXX) |
But I want this :
sum | city | geom |
---|---|---|
18 | Paris | MULTIPOLYGON(XXX) |
10 | Berlin | MULTIPOLYGON(XXX) |
70 | Milan | MULTIPOLYGON(XXX) |
51 | London | MULTIPOLYGON(XXX) |
How can I add the city geometry and still have unique city values and execute the SUM?
Important note : the geometries are multipolygons
I am using PostgreSQL
4
Answers
Perhaps your problem is more suitable to use INNER JOIN.
Exemple:
Perhaps some of the cities does not have the same geom value as the others. Try this one to see the differences
This ensures that the city is unique, and if there are more than one geometry per city, it will select only one:
Without knowing the data, I imagine this could be happening because there are multiple examples of geometry per city, but I also agree with Francisco in not using:
This is a CROSS JOIN which I am not sure is intended here if you are simply trying to join over the geometry field.