pivot-table and group geometries

Question

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 :

geom|count(a)|count(b)|count(c)
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 ?

Answer 1

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;