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 Путлер Капут 六四事, 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 234 · Accepted Answer · edited Sep 10 '21 at 03:09

234

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

389
2
15

answered Sep 02 '12 at 11:34

Mihai Stancu

15,260
2
32
49

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
8

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

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

61

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,260
2
32
49

answered Sep 02 '12 at 11:33

Mat

195,986
40
382
396

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 30 · Answer 3 · edited Aug 03 '13 at 04:17

30

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,863
2
21
37

answered Aug 03 '13 at 03:49

naveen

301
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 15 · 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

704,970
168
1,303
1,425

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

130,641
51
193
212

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

10,266
3
41
71

answered Apr 01 '19 at 14:30

Mayur Mane

13
1
7

score -2 · Answer 9 · edited Mar 10 '22 at 10:39

-2

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

1,408
1
6
12

answered May 06 '19 at 10:14

Omar Lari

19
1

Find most frequent value in SQL column

9 Answers9

Linked

Related