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.
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.