Sorting words by their presence in the database, matching similar words

Question

I have a mysql table, there are many words on it.

In the above example, "hi" happens three times. I want to create a query that will look at this table and sort the most common words.

+3

geekish fellow Apr 18 15 at 11:22

2 answers

You need to link the sql statement using a method group by

that will group things together. Something like this should get you started.

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

0

habib ul haq Apr 18 15 at 11:29

fracz · Accepted Answer · 2015-04-18T11:25:42+0000

You need to use GROUP BY and COUNT

.

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

Possible way out:

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

If you want MySQL to treat hai

both Hai.

as the same word, remove all non-alpha characters before grouping. See MySQL: How to remove all non-alphabetic numeric characters from a string? ... Then, based on the method in this answer , it looks 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 the possible result:

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