Geek asks and answers

Most common values ​​in a MySQL database

How can you select the most common values ​​in a MySQL database? Assuming I have a field numwith these lines:

1, 1, 3, 1, 1, 17, 12, 1, 3, 17, 3

If I wanted to find the three most common values, 1, 3, and 17, how would I do this (and get the score)?

Will the correct method fit SELECT UNIQUEand count for each individual value? Is there a more efficient method? It looks like this will crash large datasets.

Thanks for the help! This is in PHP, with a MySQL database.

+3
source share
2 answers

Something like this should work:

SELECT num, COUNT(num) AS ct
FROM yourtable
GROUP BY num
ORDER BY ct DESC

MySQL, num. , COUNT(num) , num, ORDER BY, , num.

:

1, 1, 3, 1, 1, 17, 12, 1, 3, 17, 3

:

   num  | ct
============
     1  |  5
     3  |  3
    17  |  2
    12  |  1
+15

MySQL Cookbook , -

SELECT num, COUNT(num) AS occurrence
FROM table
GROUP BY num
0

More articles:


All Articles