How to Find ALL Most Common Values in SQL?

Question

How would I go about showing ALL most common values in SQL?

So I have the query to show the most common value here bellow.

SELECT name, COUNT(*) AS popularity
FROM cattwo 
GROUP BY name 
ORDER BY popularity DESC 
LIMIT 1;

    +----------+------------+
    | name     | popularity |
    +----------+------------+
    | cat22610 |          7 |
    +----------+------------+

However, when I display the top 10 most common value the result is ...

SELECT name, COUNT(*) AS popularity 
FROM cattwo 
GROUP BY name 
ORDER BY popularity DESC 
LIMIT 10;

    +----------+------------+
    | name     | popularity |
    +----------+------------+
    | cat22610 |          7 |
    | cat68704 |          7 |
    | cat14153 |          7 |
    | cat52476 |          7 |
    | cat4556  |          7 |
    | cat64173 |          7 |
    | cat5586  |          7 |
    | cat89302 |          6 |
    | cat97131 |          6 |
    | cat42010 |          6 |
    +----------+------------+

The goal is to display all cats with the highest popularity. Something like this.

+----------+------------+
| name     | popularity |
+----------+------------+
| cat22610 |          7 |
| cat68704 |          7 |
| cat14153 |          7 |
| cat52476 |          7 |
| cat4556  |          7 |
| cat64173 |          7 |
| cat5586  |          7 |
+----------+------------+

Help would be great. Thanks in advance.

score 1 · Answer 1 · answered Jun 18 '14 at 04:14

SELECT name, COUNT(*) AS popularity 
FROM cattwo 
GROUP BY name 
HAVING COUNT(*) = 
        (
            SELECT COUNT(*) AS popularity 
            FROM cattwo 
            GROUP BY name
            ORDER BY popularity DESC
            LIMIT 1
        )
LIMIT 10;

SQLFiddle Demo

ngrashia · Accepted Answer · 2014-06-18T04:32:43.420

1

I guess this query would help you!

SELECT NAME, COUNT(*) AS POPULARITY 
FROM CATTWO 
GROUP BY NAME 
HAVING COUNT(*) = 
        (
            SELECT COUNT(*) AS MAX_POPULARITY 
            FROM CATTWO 
            GROUP BY NAME
            ORDER BY MAX_POPULARITY DESC
            LIMIT 1
        );

edited Jun 18 '14 at 04:32

answered Jun 18 '14 at 04:20

ngrashia

9,869
5
43
58

score 0 · Answer 3 · answered Jun 18 '14 at 04:21

0

select C1.name,COUNT(*) AS  popularity FROM cattwo C1 GROUP BY C1.name

HAVING 0=(SELECT COUNT(*) popularity  FROM  cattwo 
C2 GROUP BY C2.NAME HAVING C1.popularity <C2.popularity )

answered Jun 18 '14 at 04:21

Lijo

6,498
5
49
60

How to Find ALL Most Common Values in SQL?

3 Answers3