Find the most common value in an SQL column

Question

How to find the most common value in a given column of a SQL table?

For example, for this table, it should return two , since this is the most common value:

 one two two three 

Answer 1

 SELECT 'column', COUNT('column') AS 'value_occurrence' FROM 'my_table' GROUP BY 'column' ORDER BY 'value_occurrence' DESC LIMIT 1; 

Replace column and my_table . Increment 1 if you want to see the N most common column values.

Answer 2

Try something like:

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

Answer 3

Consider the table name as tblperson and the column name as city . I want to get the most repeating 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.

Answer 4

A good query works in the SQL Server database below:

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

Answer 5

For use with SQL Server.

Since there is no limit command support in this.

Yo can use the top 1 command to find the maximum value in a 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; 

Answer 6

The proposed table is “ SalesLT.Customer ”, and the column you are trying to find 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; 

Answer 7

If you cannot use LIMIT or LIMIT, this is not an option for your query tool. Instead, you can use "ROWNUM", but you will need an additional 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 

Answer 8

If you have an identifier column and you want to find the most duplicate category from another column for each identifier, then you can use the query below,

Table:

Inquiry:

CHOOSE ID, CATEGORY, ACCOUNT (*) HOW FREQUENCY FROM ROW_NUMBER QUALIFICATION TABLE GROUP 1.2 ROTATE OVER (FREQ DESC IDENTIFICATION SEPARATION) = 1;

Result:

Answer 9

One way that I like to use:

select COUNT () as VAR1 from Table_Name

group by

order by VAR1 desc

limit 1