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

Question

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:51

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     |
+------+-------+

If i want to include the words which are seems like similar means what to do?. — geekish fellow, Apr 18 '15 at 11:27
In at above out put basically hai and Hai are similar if i want to count hai and Hai are same what to do?. — geekish fellow, Apr 18 '15 at 11:31

score 0 · Answer 2 · edited Apr 18 '15 at 16:25

0

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

edited Apr 18 '15 at 16:25

Dave Bennett

answered Apr 18 '15 at 11:29

habib ul haq

2 Answers2