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
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;
Source: Stackoverflow.com