CODEWITHSUNDEEP
mysqlsql
Jake
Jake

Reputation: 3486

Find most frequent value in SQL column

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

Upvotes: 171

Views: 399025

Answers (11)

DAGWOOD
DAGWOOD

Reputation: 1

SELECT     TOP 20 WITH TIES COUNT(Counted_Column) AS Count, OtherColumn1, 
OtherColumn2, OtherColumn3, OtherColumn4
FROM         Table_or_View_Name
WHERE
(Date_Column >= '01/01/2023') AND 
(Date_Column <= '03/01/2023') AND 
(Counted_Column = 'Desired_Text')
GROUP BY OtherColumn1, OtherColumn2, OtherColumn3, OtherColumn4
ORDER BY COUNT(Counted_Column) DESC
  • 20 can be changed to any desired number
  • WITH TIES allows all ties in the count to be displayed
  • Date range used if date/time column exists and can be modified to search a date range as desired
  • Counted_Column 'Desired_Text' can be modified to only count certain entries in that column
  • Works in INSQL for my instance

Upvotes: 0

Ciro Santilli OurBigBook.com
Ciro Santilli OurBigBook.com

Reputation: 382822

Return all most frequent rows in case of tie

Find the most frequent value in mysql,display all in case of a tie gives two possible approaches:

  1. Scalar subquery:

    SELECT
  "country",
  COUNT(country) AS "cnt"
FROM "Sales"
GROUP BY "country"
HAVING
  COUNT("country") = (
    SELECT COUNT("country") AS "cnt"
    FROM "Sales"
    GROUP BY "country"
    ORDER BY "cnt" DESC,
    LIMIT 1
  )
ORDER BY "country" ASC

  2. With the RANK window function, available since MySQL 8+:

    SELECT "country", "cnt"
  FROM (
    SELECT
      "country",
      COUNT("country") AS "cnt",
      RANK() OVER (ORDER BY COUNT(*) DESC) "rnk"
    FROM "Sales"
    GROUP BY "country"
  ) AS "sub"
WHERE "rnk" = 1
ORDER BY "country" ASC

    This method might save a second recount compared to the first one.

    RANK works by ranking all rows, such that if two rows are at the top, both get rank 1. So it basically directly solves this type of use case.

    RANK is also available on SQLite and PostgreSQL, I think it might be SQL standard, not sure.

In the above queries I also sorted by country to have more deterministic results.

Tested on SQLite 3.34.0, PostgreSQL 14.3, GitHub upstream.

Most frequent for each GROUP BY group

Upvotes: 0

Omar Lari
Omar Lari

Reputation: 29

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

Upvotes: -1

Mihai Stancu
Mihai Stancu

Reputation: 16107

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.

Upvotes: 271

Mayur Mane
Mayur Mane

Reputation: 13

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:

Table content

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:

Query result

Upvotes: 0

Swadhikar
Swadhikar

Reputation: 2210

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

Upvotes: 17

Muzammel Mukul
Muzammel Mukul

Reputation: 21

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;

Upvotes: 2

Roadkill
Roadkill

Reputation: 9

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

Upvotes: 0

Muneeb Hassan
Muneeb Hassan

Reputation: 125

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;

Upvotes: 3

naveen
naveen

Reputation: 321

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.

Upvotes: 32

Mat
Mat

Reputation: 206689

Try something like:

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

Upvotes: 66

Related Questions