mysqlsqlcountpopularity

How to Find ALL Most Common Values in SQL?


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.


Solution

  • 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
            );