SQL Tutorial – Calculate Mode in SQL.

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.