Using unnest in multiple rows

Question

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;

Answer 1

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