Stanislav Belyakov Stanislav Belyakov - 2 years ago 175
SQL Question

PostgreSQL: get count of occurrences of specified element in array

I need to calculate the count of occurrences of specified element in array, something like:

elem_occurrences_count(ARRAY[a,b,c,a,a], a) = 3

elem_occurrences_count(ARRAY[a,b,c], d) = 0

Is there any function in PostgreSQL that can be used to solve the problem? Any help is appreciated.

Answer Source

You will need to unnest the array and then count the occurrences.

with elements (element) as (
   select unnest(ARRAY['a','b','c','a','a'])
select count(*)
from elements
where element = 'a';

This can easily be embedded into a function:

create or replace function count_elements(elements text[], to_find text)
  returns bigint
  select count(*) 
  from unnest(elements) element 
  where element =  to_find;
language sql;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download