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

User · Accepted Answer

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

User · Answer

The HAVING option can be used for this purpose and query should be SELECT word  COUNT    FROM words  GROUP BY word HAVING COUNT     gt  1

User · Answer

SELECT word  COUNT    FROM words GROUP by word HAVING COUNT     gt  1

User · Answer

SELECT count word  as count  FROM words  GROUP BY word HAVING count  gt   2

[mysql] MySQL count occurrences greater than 2

Examples related to mysql