skip to Main Content

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


  1. Perhaps your problem is more suitable to use INNER JOIN.
    Exemple:

    SELECT SUM(tablea.sum), tableb.city, tableb.geom
    FROM tablea INNER JOIN tableb 
    ON tablea.id = tableb.id
    
    Login or Signup to reply.
  2. Perhaps some of the cities does not have the same geom value as the others. Try this one to see the differences

    SELECT SUM(tablea.sum), tableb.city, ST_X(tableb.geom), ST_Y(tableb.geom)
    FROM tablea INNER JOIN tableb 
    ON tablea.id = tableb.id
    GROUP BY tableb.city, ST_X(tableb.geom), ST_Y(tableb.geom)
    
    Login or Signup to reply.
  3. This ensures that the city is unique, and if there are more than one geometry per city, it will select only one:

    SELECT  tableb.city, SUM(tablea.sum), max(tableb.geom)
    FROM tablea
    INNER JOIN tableb ON tablea.id = tableb.id
    GROUP BY tableb.city
    
    Login or Signup to reply.
  4. 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:

    FROM tablea, tableb
    

    This is a CROSS JOIN which I am not sure is intended here if you are simply trying to join over the geometry field.

    SELECT
       SUM(tablea.sum)
       , tableb.city
       , tableb.geom
    FROM tablea
       JOIN tableb 
          ON tablea.id = tableb.id
    GROUP BY
       tableb.city
       , tableb.geom
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search