Aggregate Mode

From PostgreSQL wiki

The statistical mode is the value that appears most often in a set of values.

mode()

PostgreSQL makes it easy to add custom aggregate functions. This snippet is also part of the ulib_agg user-defined library.

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. 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
);

Usage

SELECT mode(some_value) AS modal_value FROM t;

Caution

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

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.

See also

• Aggregate Range
• Aggregate Median
• most_common_vals() pg_stats function

External links

• A very simple custom aggregate