Find most frequent value in SQL column

Question

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

GROUP BY version: http://stackoverflow.com/questions/344665/get-most-common-value-in-sql — Ciro Santilli OurBigBook.com, Sep 07 '15 at 19:33
What about ties? Add another 'three' row to the sample data, and specify the expected result. — jarlh, Nov 18 '19 at 10:55

score 259 · Accepted Answer · edited Sep 10 '21 at 03:09

259

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.

edited Sep 10 '21 at 03:09

Insung Park

401
3
15

answered Sep 02 '12 at 11:34

Mihai Stancu

15,848
2
33
51

2

what what should I do if I want to select another fields too from 'my_table'? In the other words, another value; – grep Jul 10 '15 at 11:59
10

what if more than one value appear same no of times (which is max)? In this case, if three also appeared twice? LIMIT 1 will show only one record – mustafa1993 Jan 17 '16 at 18:44
1

@mustafa1993 `SELECT * FROM my_table GROUP BY value ORDER BY count(*) DESC;` – Ahmed Syed Nov 04 '16 at 05:27
why doesn't it work when i add `WHERE 'value_occurrence' = 1` ? – swisswiss Jan 22 '18 at 22:18
1

@swisswiss you must use `HAVING` instead of `WHERE` in this case. – HellBaby Feb 21 '18 at 15:18
Do all the accents (`) have any purpose in this query? – compie Nov 06 '19 at 21:50
@mustafa1993 I've covered the case where you want to get all that match the moast frequent at: https://stackoverflow.com/a/72979899/895245 – Ciro Santilli OurBigBook.com Jul 14 '22 at 16:57

score 64 · Answer 2 · edited Sep 02 '12 at 11:42

64

Try something like:

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

edited Sep 02 '12 at 11:42

Mihai Stancu

15,848
2
33
51

answered Sep 02 '12 at 11:33

Mat

202,337
40
393
406

6

I didn't know that you could use `COUNT(*)` directly in the `ORDER BY`. I knew there were a few restrictions regarding `GROUP BY`/`HAVING` and aggregate columns and I always assumed that wouldn't work. – Mihai Stancu Sep 02 '12 at 11:40

score 32 · Answer 3 · edited Aug 03 '13 at 04:17

32

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.

edited Aug 03 '13 at 04:17

Simon M

2,931
2
22
37

answered Aug 03 '13 at 03:49

naveen

321
3
2

1

+1 for using standard SQL that will work in any database (whereas LIMIT is MySQL specific, TOP is SQL Server specific). – Dylan Smith Jul 20 '18 at 14:46

score 17 · Answer 4 · answered Jan 29 '16 at 09:48

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

score 3 · Answer 5 · edited Apr 07 '15 at 18:40

3

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;

edited Apr 07 '15 at 18:40

marc_s

732,580
175
1,330
1,459

answered Dec 26 '12 at 23:26

Muneeb Hassan

125
12

You also need to move COUNT function to ORDER BY section to avoid getting the following error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS – Saba Jamalian Jan 22 '16 at 21:12

score 2 · Answer 6 · edited Dec 28 '15 at 07:34

2

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;

edited Dec 28 '15 at 07:34

ρяσѕρєя K

132,198
53
198
213

answered Dec 28 '15 at 07:28

Muzammel Mukul

21
1

score 0 · Answer 7 · answered Nov 24 '15 at 16:59

0

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

answered Nov 24 '15 at 16:59

Roadkill

9
2

MySQL doesn't have `ROWNUM` – Barmar Dec 28 '15 at 15:53
This is valid for oracle but not mysql – Prabhu Sep 11 '16 at 05:29
1

@Prabhu in MySQL, you use `LIMIT 1` instead; syntax is shown in the accepted answer. – ToolmakerSteve Jan 12 '17 at 00:41

score 0 · Answer 8 · edited Jan 31 '20 at 11:26

0

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

edited Jan 31 '20 at 11:26

Chris Schaller

13,704
3
43
81

answered Apr 01 '19 at 14:30

Mayur Mane

13
1
1
7

Ciro Santilli OurBigBook.com · Answer 9 · 2022-07-14T18:03:53.450

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:

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

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

DAGWOOD · Answer 10 · 2023-02-17T21:57:05.000

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

score -1 · Answer 11 · edited Mar 10 '22 at 10:39

-1

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

edited Mar 10 '22 at 10:39

RF1991

2,037
4
8
17

answered May 06 '19 at 10:14

Omar Lari

29
1

Find most frequent value in SQL column

11 Answers11

Linked

Related