Find most frequent value in SQL column
MysqlSqlMysql Problem Overview
How can I find the most frequent value in a given column in an SQL table?
For example, for this table it should return two
since it is the most frequent value:
one
two
two
three
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
Try something like:
SELECT `column`
FROM `your_table`
GROUP BY `column`
ORDER BY COUNT(*) DESC
LIMIT 1;
Solution 3 - Mysql
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 - Mysql
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
..
..
Solution 5 - Mysql
For use with SQL Server.
As there is no limit command support in that.
Yo can use the top 1 command to find the maximum occurring value in the particular column in this case (value)
SELECT top1
`value`,
COUNT(`value`) AS `value_occurrence`
FROM
`my_table`
GROUP BY
`value`
ORDER BY
`value_occurrence` DESC;
Solution 6 - Mysql
Assuming Table is 'SalesLT.Customer
' and the Column you are trying to figure out is 'CompanyName
' and AggCompanyName
is an Alias.
Select CompanyName, Count(CompanyName) as AggCompanyName from SalesLT.Customer
group by CompanyName
Order By Count(CompanyName) Desc;
Solution 7 - Mysql
If you can't use LIMIT or LIMIT is not an option for your query tool. You can use "ROWNUM" instead, but you will need a sub query:
SELECT FIELD_1, ALIAS1
FROM(SELECT FIELD_1, COUNT(FIELD_1) ALIAS1
FROM TABLENAME
GROUP BY FIELD_1
ORDER BY COUNT(FIELD_1) DESC)
WHERE ROWNUM = 1
Solution 8 - Mysql
If you have an ID column and you want to find most repetitive category from another column for each ID then you can use below query,
Table:
Query:
SELECT ID, CATEGORY, COUNT(*) AS FREQ
FROM TABLE
GROUP BY 1,2
QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY FREQ DESC) = 1;
Result:
Solution 9 - Mysql
One way I like to use is:
select *<given_column>*,COUNT(*<given_column>*)as VAR1 from Table_Name
group by *<given_column>*
order by VAR1 desc
limit 1