GROUP BY the number of rows returned by GROUP BY in MySQL

Question

I have a table - for the purposes of this question, it has one column word. The following information is in this table:

• apple - appears 3 times
• banana - appears 5 times
• pear - appears 3 times

I wrote the following SQL code to select each word uniquely, along with the number of times that word appears in the table:

SELECT word, COUNT(word) FROM table GROUP BY word ORDER BY COUNT(word) DESC;

This returns me the following information:

--------------------------
| word     | COUNT(word) |
--------------------------
| banana   | 5           |
--------------------------
| apple    | 3           |
--------------------------
| pear     | 3           |
--------------------------

Is there a way to write a query to return the following information:

-----------------------------
| COUNT(word) | words       |
-----------------------------
| 5           | banana      |
-----------------------------
| 3           | apple, pear |
-----------------------------

I was thinking along the lines of double GROUP BY, but haven't thought of much yet.

Answer 1

You can use GROUP_CONCAT:

SELECT word_count, GROUP_CONCAT(word SEPARATOR ', ') as words
FROM (SELECT word, COUNT(word) as word_count
      FROM table 
      GROUP BY word) w
GROUP BY word_count
ORDER BY word_count DESC;