CODEWITHSUNDEEP
mysql
mrpatg
mrpatg

Reputation: 10117

count top 10 most occuring values in a column in mysql

I have a column in mysql table that has the the data type INT(11).

How can I search to get the top 10 most occurring values in this column?

Upvotes: 6

Views: 16852

Answers (4)

Josh Davis
Josh Davis

Reputation: 28730

Try the following code

SELECT colname, COUNT(*) AS cnt
FROM tablename
GROUP BY colname
ORDER BY cnt DESC
LIMIT 10

Upvotes: 4

Ben
Ben

Reputation: 11208

TOP is a keyword which is not supported in MySQL, it is in MSSQL though.

This following query should do what you want (untested, but the idea should become clear):

SELECT column, COUNT(*) AS matches 
FROM table 
GROUP BY column 
ORDER BY matches DESC 
LIMIT 10

Upvotes: 3

Michael Krelin - hacker
Michael Krelin - hacker

Reputation: 143229

SELECT col, count(*)
    FROM tablethingie
    GROUP BY col
    ORDER BY count(*) DESC
    LIMIT 10

Upvotes: 29

jammus
jammus

Reputation: 2550

Try:

SELECT ColName, Count(1) AS occurances
    FROM
        table
    GROUP BY
        ColName
    ORDER BY
        occurances DESC
    LIMIT
        10

Upvotes: 2

Related Questions