In statistics, the mode is the most commonly occurring value in a data set. When using SQL databases, we can easily run a SQL query to find out the mode of a given column.

Below are several options we can use to calculate the mode in SQL.

Sample Data

The examples on this page use 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

We can see that vendor 1001 occurs the most in the vendorid column. Therefore, the mode is 1001.

The MODE() Function

Some RDBMSs have a MODE() function that’s designed specifically to calculate the mode. If your RDBMS has such a function, then this is probably your easiest option when it comes to calculating the mode in SQL.

The actual syntax you use will depend on your RDBMS.

In some RDBMSs, you can simply pass the column name to the MODE() function, like this:

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

The COUNT() Function

If your RDBMS doesn’t have a MODE() function, you could try constructing a query using the SQL COUNT() function to produce the desired results.

The exact syntax you use will depend on your RDBMS.

In most RDBMSs we can use a query like this:

SELECT 
    vendorid, 
    COUNT(*) AS vendorcount
FROM products
GROUP BY vendorid
ORDER BY vendorcount DESC
LIMIT 1;

Result:

+----------+-------------+
| vendorid | vendorcount |
+----------+-------------+
|     1001 |           4 |
+----------+-------------+

In this example I added another column that tells us the actual number of occurrences of the value. So, not only do we get the mode, we also get the number of times the value appears in the dataset. We can simply omit this column if we don’t need it.

The above query should work in most of the major RDBMSs. I ran it in MySQL, MariaDB, SQLite, and PostgreSQL without any problems.

And if we omit the LIMIT clause, the query should run in pretty much any RDBMS. But omitting the LIMIT clause will cause all rows to be returned (with the first row containing the mode, due to sorting the results in descending order).

If you use SQL Server or Oracle, and you want to limit the results to one row like in the above query, try one of the solutions below.

SQL Server

In SQL Server (and Azure SQL Edge), we can run the following query:

SELECT 
    TOP(1) vendorid, 
    COUNT(*) AS vendorcount
FROM products
GROUP BY vendorid
ORDER BY vendorcount DESC;

Result:

vendorid  vendorcount
--------  -----------
1001      4          

The difference between SQL Server and most other RDBMSs is that SQL Server uses TOP() instead of the LIMIT clause.

Oracle

In Oracle Database, we can replace the LIMIT clause with the FETCH row limiting clause:

SELECT 
    vendorid, 
    COUNT(*) AS vendorcount
FROM Products
GROUP BY vendorid
ORDER BY vendorcount DESC
FETCH FIRST 1 ROW ONLY;

Or we could use the ROWNUM pseudocolumn, like this:

SELECT * FROM (
	SELECT
           VendorId, 
           COUNT(*) AS VendorCount 
	FROM Products
        GROUP BY VendorId
	ORDER BY VendorCount DESC    
    )
WHERE ROWNUM <=1;

Result:

VENDORID	VENDORCOUNT
--------        -----------
1001	        4

Many SQL databases (including Oracle) also have a window function called ROW_NUMBER() that will enable us to do a similar thing as the ROWNUM pseudocolumn, but with possibly better ordering capabilities.