Group by an array's element in PostgreSQL

Question

In Postgres 8.3, I need to group by certain elements within an array field. When I group by the array itself, all possible combinations are shown. I only want to group by the element.

something like this works for finding the count of a single element:

SELECT count(*)
FROM table
WHERE foo=any(bar)

this will return the correct count for a single element in an array. How do I return multiple counts for all elements in an array? If I group by the array, it will use all array elements in the identical order they are stored (not what I need).

edit for clarity: bar is an array with values like {foo, some, thing} or {foo, thing} or {some, thing, else}

I want to know how many records have an element value of foo, some, thing, and else in the array "bar".

Peter Eisentraut · Accepted Answer · 2011-09-29 04:02:55Z

1

Something like

GROUP BY bar[4]

answered Sep 29, 2011 at 4:02

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Over a year ago

is this the actual syntax? I am running 8.3, and not getting it to work with your syntax. I will update my question with a better example.

Alex · Accepted Answer · 2011-09-28 20:36:58Z

0

Hm.. you can't group by a value.. but of course the value is the column that contains it... So.. you might want this:

SELECT AVG(blah), foo
FROM whatever
WHERE foo=any(bar)
GROUP BY foo

should work..

answered Sep 28, 2011 at 20:36

Alex

2,1463 gold badges25 silver badges48 bronze badges

1 Comment

Josh Over a year ago

that doesn't work for me. if bar = {f,o,o} your example is: SELECT AVG(), f FROM whatever WHERE f=any(bar) group by f

Collectives™ on Stack Overflow

Group by an array's element in PostgreSQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related