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

README.md

Count elements

Basic example

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

-[ RECORD 1 ]--+------------------------------
count_elements | 3(5)  2(5)  1(4)  4(2)  5(1)  43(1)

Real world example

SELECT state, count(*), count_elements(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_elements.sql

CREATE FUNCTION count_elements(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;