0

I have an array column containing information like

{a,b,c,a} 
{a,b,a,b}
{b,a,a,b}

I want to run unnest on this array column, get the occurence of of each character.

a 2
b 1
c 1
a 2
b 2
b 2
a 2

I run the following query which works when considered for one row:

SELECT i, count(*) AS count
FROM   unnest(array::text[]) i
GROUP  BY i
ORDER  BY i;
0

You can use the query below;

SELECT
  unnest(i),
  count(*)
FROM (
  VALUES 
    ('{a,b,c,a}'::text[]), 
    ('{a,b,a,b}'), 
    ('{b,a,a,b}')
) AS t(i)
GROUP BY 1, i   -- group by column value, you can use any unique field here
ORDER BY i

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.