You want to calculate the Mode value of a column. A Mode is the value that occurs most frequently in a given data set or a column. For example, here we have some data about employees and we want to find out the mode value of the salary column.

Solution –
To calculate the Mode, first we have to count the occurrences of each unique salaries in the salary column.
SELECT
salary,
COUNT(*) as cnt
FROM
employees
GROUP By
salary

Next, we will have to use the DENSE_RANK window function to rank these counts of the salaries.
SELECT
salary,
cnt,
DENSE_RANK() OVER(
ORDER BY
cnt DESC
) as rnk
FROM
(
SELECT
salary,
COUNT(*) as cnt
FROM
employees
GROUP By
salary
) x

And once we have these ranking all we need to do is use a subquery and select the rows where rnk = 1.
SELECT
salary as mode_Salary
FROM
(
SELECT
salary,
cnt,
DENSE_RANK() OVER(
ORDER BY
cnt DESC
) as rnk
FROM
(
SELECT
salary,
COUNT(*) as cnt
FROM
employees
GROUP By
salary
) x
) y
WHERE
rnk = 1

The Mode of the salary is $5000 and $4000 because both these values occurs twice in the salary column and none other values in this column occurs more than twice. Also when we have Two Modes we call it a Bimodal distribution.
Related Post – How to calculate Median in SQL.
If you like this post then please share it with others and subscribe to our blog below to learn data science.