[mysql] MySQL count occurrences greater than 2

I have the following table structure

+  id  +  word  +
+------+--------+

The table gets filled with the words in lower cas of a given text, so the text

Hello bye hello

would result in

+  id  +  word  +
+------+--------+
+   1  + hello  +
+------+--------+
+   2  + bye    +
+------+--------+
+   3  + hello  +
+------+--------+

I want to make a SELECT query that will return the number of words that get repeated at least two times in the table (like hello)

SELECT COUNT(id) FROM words WHERE (SELECT COUNT(words.word))>1

which of course is so wrong and super overloading when table is big. Any idea on how to achieve such purpose? In the given example inhere-above, I would expect 1

This question is related to mysql

The answer is


SELECT count(word) as count 
FROM words 
GROUP BY word
HAVING count >= 2;

SELECT word, COUNT(*) FROM words GROUP by word HAVING COUNT(*) > 1


The HAVING option can be used for this purpose and query should be

SELECT word, COUNT(*) FROM words 
GROUP BY word
HAVING COUNT(*) > 1;