CODEWITHSUNDEEP
mysql
rev
rev

Reputation: 1861

Group repeated values by GROUP_BY with counter?

I am working with MySQL, and currently my query returns something like:

|ID|Tags|
|01|tag1,tag2,tag2,tag2
|02|tag2,tag2
|03|tag2,tag2,tag2,tag2,tag3

I'd like it to return:

|ID|Tags|
|01|tag1,tag2(x3)
|02|tag2(x2)
|03|tag2(x4),tag3

The repeated values are expected. All I want is to group them. Is it even possible?

I'm using GROUP_CONCAT(table.column), and it returns me those results.

Upvotes: 1

Views: 28

Answers (1)

John Ruddell
John Ruddell

Reputation: 25862

ok so I made a solution.. I had to play with it for a little bit... but basically you have to do a pretty nasty looking query to get this to work. if you can do this in another programming language it would be best. 

SELECT id, GROUP_CONCAT(final_tags) 
FROM(
  SELECT *
  FROM(
    SELECT 
      tag, 
      combined, 
      IF(tag <> combined, CONCAT(tag, combined), tag) AS final_tags,
      counted, 
      id 
    FROM (
      SELECT 
        id,
        tag,
        IF(@A = tag, @B := @COUNTER, @B := 1) AS counted,
        IF(@A = tag, @D := CONCAT('(x', @COUNTER, ')'), @D := tag) AS combined,
        @A := tag,
        IF(@B = 1, @COUNTER := 1, @COUNTER),
        @COUNTER := @COUNTER + 1
      FROM myTable
      JOIN (SELECT @A := '', @D := '', @COUNTER := 1, @B := 0) AS t
      ) AS temp
    ORDER BY counted DESC
    ) AS whatever
  GROUP BY tag
) AS this_is_it
GROUP BY id

SEE FIDDLE for clarification

Upvotes: 1

Related Questions