Find most frequent value in SQL column

Solution 1:

SELECT
  <column_name>,
  COUNT(<column_name>) AS `value_occurrence` 

FROM
  <my_table>

GROUP BY 
  <column_name>

ORDER BY 
  `value_occurrence` DESC

LIMIT 1;

Replace <column_name> and <my_table>. Increase 1 if you want to see the N most common values of the column.

Solution 2:

Try something like:

SELECT       `column`
    FROM     `your_table`
    GROUP BY `column`
    ORDER BY COUNT(*) DESC
    LIMIT    1;

Solution 3:

Let us consider table name as tblperson and column name as city. I want to retrieve the most repeated city from the city column:

 select city,count(*) as nor from tblperson
        group by city
          having count(*) =(select max(nor) from 
            (select city,count(*) as nor from tblperson group by city) tblperson)

Here nor is an alias name.

Solution 4:

Below query seems to work good for me in SQL Server database:

select column, COUNT(column) AS MOST_FREQUENT
from TABLE_NAME
GROUP BY column
ORDER BY COUNT(column) DESC

Result:

column          MOST_FREQUENT
item1           highest count
item2           second highest 
item3           third higest
..
..