PostgreSQL has an ordered-set aggregate function called mode() that allows us to get the mode from a given column.

The mode is the most frequently occurring value.

Null values are ignored, so if null occurs the most, the mode() function will return the second most common value.

Syntax

The syntax goes like this:

mode () WITHIN GROUP ( ORDER BY anyelement )

So it doesn’t accept any arguments, but we must use a WITHIN GROUP clause with an ORDER BY clause.

Example

Suppose we have a table with the following data:

SELECT
    vendorid,
    productname,
    productprice
FROM products;

Result:

 vendorid |           productname           | productprice 
----------+---------------------------------+--------------
     1001 | Left handed screwdriver         |        25.99
     1001 | Right handed screwdriver        |        25.99
     1001 | Long Weight (blue)              |        14.75
     1001 | Long Weight (green)             |        11.99
     1002 | Sledge Hammer                   |        33.49
     1003 | Chainsaw                        |       245.00
     1003 | Straw Dog Box                   |        55.99
     1003 | Hammock                         |        10.00
     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99
     1004 | Tea Pot                         |        12.45

In this table, each row contains a different product, and it includes the ID of the vendor for that product.

If we wanted to find out which vendor has the most products, we could use the mode() function to find out which vendorid value occurs the most in this table:

SELECT 
    mode() WITHIN GROUP ( ORDER BY vendorid )
FROM products;

Result:

 mode 
------
 1001

So it tells us that vendor 1001 occurs the most. Therefore, vendor 1001 has the most products in the table.

Another thing we could do with this data is find out which price occurs the most. In this case all we’d need to do is replace ORDER BY vendorid with ORDER BY productprice:

SELECT 
    mode() WITHIN GROUP ( ORDER BY productprice )
FROM products;

Result:

 mode  
-------
 25.99

Ties

If more than one row contains the most frequently occurring value, the mode() function will choose the first one. This logic also applies in the case of unique values.

We can see that the productname column in our table contains unique values. Therefore, mode will be pretty much irrelevant for that column, given each value only occurs once. Another way of putting it is that all rows qualify equally for containing the most frequently occurring value.

That said, we can still apply the mode() function to that column:

SELECT 
    mode() WITHIN GROUP ( ORDER BY productname )
FROM products;

Result:

              mode               
---------------------------------
 Bottomless Coffee Mugs (4 Pack)

In this case, it returned the value from the first row, based on the specified order.

Removing the ORDER BY Clause

As mentioned, the ORDER BY clause is required. Removing it results in an error:

SELECT 
    mode() WITHIN GROUP ( )
FROM products;

Result:

syntax error at or near ")"

Removing the WITHIN GROUP Clause

It probably goes without saying that removing the WITHIN GROUP clause also results in an error, but here it is anyway:

SELECT 
    mode()
FROM products;

Result:

function mode() does not exist

This time we got a different error. We’re told that the mode() function doesn’t exist, even though we know that it does. However, the function doesn’t exist with that syntax, so it’s not necessarily wrong.