CODEWITHSUNDEEP
sqlarrayspostgresqlgroup-by
Josh
Josh

Reputation: 177

Group by an array's element in PostgreSQL

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

Upvotes: 3

Views: 1560

Answers (2)

Peter Eisentraut
Peter Eisentraut

Reputation: 36759

Something like

GROUP BY bar[4]

Upvotes: 1

Alex
Alex

Reputation: 2146

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

Upvotes: 0

Related Questions