Is it possible to group rows twice in MySQL?

Question

I have a table like this:

someid somestring 1 Hello 1 World 1 Blah 2 World 2 TestA 2 TestB ... 

I am currently grouping by id and concatenating strings, so I end up with:

 1 Hello,World,Blah 2 World,TestA,TestB ... 

Is it possible to make a second grouping, so that if there are several records that end on one line, I can also group them?

Answer 1

Yes, just put your current query in the inner selection and apply the new GROUP BY to the outer selection. Note that you probably want to use ORDER BY GROUP_CONCAT to ensure that the rows will always be merged in the same order.

 SELECT somelist, COUNT(*) FROM ( SELECT someid, GROUP_CONCAT(somestring ORDER BY somestring) AS somelist FROM table1 GROUP BY someid ) AS T1 GROUP BY somelist 

Result:

 'Blah,Hello,World', 1 'TestA,TestB,World', 2 

Here we used test data:

 CREATE TABLE table1 (someid INT NOT NULL, somestring NVARCHAR(100) NOT NULL); INSERT INTO table1 (someid, somestring) VALUES (1, 'Hello'), (1, 'World'), (1, 'Blah'), (2, 'World'), (2, 'TestA'), (2, 'TestB'), (3, 'World'), (3, 'TestB'), (3, 'TestA');