Count elements in a pgsql array_agg. Adapted to work with any typed array (numeric, text etc.) and more machine-readable format from https://wiki.postgresql.org/wiki/Count_elements_in_Array

README.md

Count elements

See this gist for a more human readable version https://gist.github.com/mhkeller/4a2ab0e6bf229a6f2bc2

Basic example

select count_els('{1,2,3,4,2,1,2,3,4,5,3,1,2,3,43,1,2,3}'::text[]);

-[ RECORD 1 ]--+------------------------------
count_els | 3|5||2|5||1|4||4|2||5|1||43|1

Real world example

SELECT state, count(*), count_els(array_agg(years)) as incident_years FROM state_pollution_violations GROUP BY state

 state | count |       incident_years
-------+-------+----------------------------
 18    | 16186 | 1990|8573||1992|7534||1993|79
 48    | 26030 | 1986|15324||1990|10268||1991|438
 46    |  5145 | 1990|3001||1991|2035||1992|109
 51    |  8961 | 1987|5844||1988|3029||1989|88
 02    |   444 | 1989|316||1990|120||1991|8

count_els.sql

CREATE OR REPLACE FUNCTION count_els(anyarray) RETURNS text AS $BODY$
SELECT string_agg(i || '|' || c, '||' ) FROM (SELECT i, count(*) c FROM (SELECT unnest($1::anyarray) i) i GROUP BY i ORDER BY c DESC) foo;
$BODY$
LANGUAGE SQL;