Question 136925 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL group by to get unique values and add a column

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

Tags:

4

Answers


  1. 0

    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. 0

    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. 0

    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. 0

    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