MySQL count occurrences greater than 2

Question

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

Answer 1

To get a list of the words that appear more than once together with how often they occur, use a combination of GROUP BY and HAVING:

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

To find the number of words in the above result set, use that as a subquery and count the rows in an outer query:

SELECT COUNT(*) FROM (     SELECT NULL     FROM words     GROUP BY word     HAVING COUNT(*) > 1 ) T1 

Answer 2

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