CODEWITHSUNDEEP
mysqlsql
geekish fellow
geekish fellow

Reputation: 49

Sorting words by its occurence in a database, matching similar words

I have a mysql table it has lot of words on it.

In at above example "hi" is occurring three times I want to create a query which will look at this table and sort out most occurred words.

Upvotes: 3

Views: 70

Answers (2)

habib ul haq
habib ul haq

Reputation: 824

You need to compose a sql statement using the group by method that will group like things together. Something like this should get you started

select word, count(word) 
from table 
group by word 
order by count(word) desc

Upvotes: 0

fracz
fracz

Reputation: 21249

You need to use GROUP BY clause and COUNT function.

SELECT word, COUNT(*) count FROM your_table GROUP BY word ORDER BY count DESC LIMIT 0,3;

Possible output:

+------+-------+
| word | count |
+------+-------+
| hai  | 2     |
| Hai. | 1     |
| This | 1     |
+------+-------+

If you want MySQL to treat hai and Hai. as the same word, you should strip all non-alpha characters before grouping. See MySQL: how to remove all non-alpha numeric characters from a string?. Then, based on method from this answer it will look like this:

SELECT LOWER(alphanum(word)) word, COUNT(*) count FROM your_table 
GROUP BY LOWER(alphanum(word)) ORDER BY count DESC LIMIT 0,3;

And possible result:

+------+-------+
| word | count |
+------+-------+
| hai  | 3     |
| this | 1     |
| joe  | 1     |
+------+-------+

Upvotes: 1

Related Questions