Aggregate Mode

From PostgreSQL wiki

by Scott Bailey 'Artacus'

Sometime we need to find the value that occurs most often in a group. Then mathematical concept is mode. PostgreSQL makes it easy to add custom aggregate functions.

Conceptually the process will be to gather each value into an array. Then once all values are in the array, we will run a function to find the most common value in our array.

If you are on PostgreSQL 8.3 or below you will need to add the unnest() function to convert an array to a set of rows.

Then you need to create a function to find the most common value in the array.

CREATE OR REPLACE FUNCTION _final_mode(anyarray)
  RETURNS anyelement AS
$BODY$
    SELECT a
    FROM unnest($1) a
    GROUP BY 1 
    ORDER BY COUNT(1) DESC, 1
    LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
 
-- Tell Postgres how to use our aggregate
CREATE AGGREGATE mode(anyelement) (
  SFUNC=array_append, --Function to call for each row. Just builds the array
  STYPE=anyarray,
  FINALFUNC=_final_mode, --Function to call after everything has been added to array
  INITCOND='{}' --Initialize an empty array when starting
);

Note: Returns error if the argument is a column of NULL values.

See also

Array Range Array Median