I have this Table

place  subplace   
A      x
A      x
B      x
A      y
B      y
A      y
A      z

When I do this query

SELECT place, count(distinct subplace) AS count_subplace
from  table 
GROUP BY place

I get result

place count_subplace
A     3
B     2

Now I want the list of the distinct elements rather than the count

I know we can use string_agg

how can I call distinct and also groupby in it

I want result something like

place subplace_list
A     x,y,z
B     x,y

I have tried this which won't work

SELECT place, string_agg(distinct subplace,',') AS list_subplace
from  table 
GROUP BY place

CodePudding user response：

Because of string_agg didn't support distinct inside.

You can try to use a subquery to distinct your result set.

Query 1:

SELECT place, string_agg(subplace,',') AS list_subplace
from  (select distinct place,subplace from t) t1
GROUP BY place

Results:

| place | list_subplace |
|-------|---------------|
|     A |         x,y,z |
|     B |           x,y |