The number of MySQL instances is more than 2

Question

I have the following table structure

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

The table is filled with words in the bottom frame of the specified text, so the text

Hello Hello hello,

will 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 will be repeated at least twice in the table (for example, hello)

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

which, of course, is so erroneous and super overloaded when the table is large. Any idea on how to achieve such a goal? In the above example, I would expect 1

Answer 1

To get a list of words that appear several times along 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 this as a subquery and count the rows in an external 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; 

Answer 3

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