ansaurus

Question

count top 10 most occuring values in a colum in mysql

Answer 1

+2 A:

Try:

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

jammus 2009-12-02 08:30:46

Answer 2

+5 A:

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

Michael Krelin - hacker 2009-12-02 08:31:02

Flagged my answer for delete (-: Is it possible to use count(col) rather than count(*) ? Hmm, and which is more efficient...

Murph 2009-12-02 08:41:29

Ok, I guess you were the first to alter the mysql :-), I delete my thus and vote this one.

Peter 2009-12-02 08:42:03

count(col) will count non-null values. Since col is what we group on, it's the same, anyway. count(*) is equivalent to count(1), but is generally recommended over the latter. count(*) is supposed to be the most efficient of all as long as it's exactly what you need.

Michael Krelin - hacker 2009-12-02 08:43:34

Peter, mysql races! ;-) But what do you mean "alter"? The only thing I've altered when editing the answer was formatting, IIRC.

Michael Krelin - hacker 2009-12-02 08:44:55

Answer 3

+2 A:

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

Ben Fransen 2009-12-02 08:32:49

Up-voted to counter the down-vote; since, so far as I can tell, your answer is equally valid to all the others. I do wish that down-voters would *explain* their down-votes, though. That way we can learn something.

David Thomas 2009-12-02 08:45:25

ricebowl, would that explain it? - http://stackoverflow.com/revisions/1831456/list - the answer was edited to mimic others after being downvoted ;-)

Michael Krelin - hacker 2009-12-02 08:47:58

Indeed, because i saw an error in my answer, so i corrected it. This way there are provided correct answers and not non-working. Keeping high the quality of answers to a question.

Ben Fransen 2009-12-02 08:59:47

Ben Fransen, I do not think you've done something severely wrong. It is more common to delete your answer and upvote the correct one, but your behaviour is by no means criminal ;-) It's just that it explains the downvote. (which was mine and I revoked it).

Michael Krelin - hacker 2009-12-02 09:11:02

No hard feelings, cheers! :)

Ben Fransen 2009-12-02 09:34:27

@hacker, that does explain, but a comment explaining the down-vote would've been faster. ...yeah; I'm maybe being lazy in my old age =)

David Thomas 2009-12-02 11:46:34

Answer 4

+3 A:

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

Josh Davis 2009-12-02 08:34:15

ansaurus

tags:

views:

answers:

count top 10 most occuring values in a colum in mysql

related questions