Postgresql11 - postgis2.4
Here is a table mytbl with points and an attribute var :

geom | var
pt      a
pt      b
pt1     a
pt1     a
pt1     b
pt2     c

I want to "rotate" the table like this :

geom count(a) count(b) count(c)
pt      1       1
pt1     2       1
pt2                      1

For now I have with...

select geom,
 (case when var like 'a' then count(a) end) ,
 (case when var like 'b' then count(b) end) ,
 (case when var like 'c' then count(c) end)
from mytbl
group by geom, var;

...the result :

pt      1           
pt                  1
pt1     2           
pt1                 1
pt2                         1

How can I "group again" the features with the same geom to get just one line per geom with all count ?


You could wrap everything in another query:

SELECT * FROM (select ...original query...) GROUP BY geom;

But it would be easier to put a filter clause on the count:

SELECT geom,
       count(*) FILTER (WHERE var = 'a'),
       count(*) FILTER (WHERE var = 'b'),
       count(*) FILTER (WHERE var = 'c')
FROM mytbl
GROUP BY geom;
  • The FILTER clause is perfect (and new for me). Thanks – Leehan Oct 11 '19 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.