Some SQL relational database management systems (RDBMSs) have a MODE() function that returns the most frequently occurring value from all row values in a column.

The mode is the value that appears most frequently in a data set.

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

If we look at the vendorid column, we can see that many rows contain the same value. For example, 1001 occurs in four rows, 1003 in three rows, and 1004 in two.

We can use the MODE() function against this table. The actual syntax we use will depend on our RDBMS.

Many RDBMSs that implement a MODE() function use a simple syntax whereby we simply pass the column name as an argument.

So using the above table, we can do the following:

SELECT 
    mode(vendorid)
FROM products;

However, if you use PostgreSQL, you may need to use the following syntax:

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

Result:

 mode 
------
 1001

It returns 1001 as expected. We have just used MODE() to find out which vendor has the most products.

We could do a similar thing to find out the most common price.

Either:

SELECT 
    mode(productprice)
FROM products;

Or if you’re using PostgreSQL 9.4 or higher:

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

Result:

 mode  
-------
 25.99