CODEWITHSUNDEEP
phpmysql
Cyclone
Cyclone

Reputation: 18285

Most frequently occurring values in MySQL database

How can you select the most frequently occurring values in a MySQL database? Assuming I have a num field, with these rows:

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

If I wanted to find the three most frequently occurring values, 1, 3, and 17, how would I go about doing this (and getting a count)?

Would the proper method be to SELECT UNIQUE and do a tally for each individual value? Is there a more efficient method? This seems like it'd fail for larger data sets.

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

Upvotes: 3

Views: 3469

Answers (2)

jterrace
jterrace

Reputation: 67063

Something like this should work:

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

This tells MySQL to group the results by the num column. By also also selecting COUNT(num) as the number of rows found for each num value, we can then issue the ORDER BY which will order the results according to how many rows each value of num has.

With these values in the table:

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

The results would be:

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

Upvotes: 15

StackExchange saddens dancek
StackExchange saddens dancek

Reputation: 3378

MySQL Cookbook has a section on generating frequency distributions, which basically comes down to something like

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

Upvotes: 0

Related Questions