modify the values achieved through group_Concat in MySql?

Question

I have the following [table a]

id     res1     res2     

1       a        f
1       b        f
1       b        f
1       c        f
2       e        g 
2       e        g
2       e        g
2       f        g

I'm getting the following after doing a group_concat

select 
  id, 
  group_concat(case when cnt = 1 then res1 else concat(cnt, ' ', res1) end) as r1,
  group_concat(case when cnt = 1 then res2 else concat(cnt, ' ', res2) end) as r2

from 
(
  select id, res1,res2, count(*) as cnt
  from [table a]
  group by id, res1,res2
) t
group by id;


id      r1          r2

1      a,2 b,c     f,2 f,f
2      3 e,f       3 g,g

The res1 column is coming fine BUT res2 column is duplicating the res1 column. Basically i want to print the value of how many times a character occurs before the character. .I want in the following format..

id     r1          r2

1      a,2 b,c     4 f
2      3 e,f       4 g

How can I achieve it ?

Answer 1

The way I would approach this is to do two rollups/aggregations, using two separate subqueries for the res1 and res2 columns. The first aggregation is over id and res1 (or res2), and obtains the counts for each letter or word. Then, aggregate again, this time only over the id, to obtain a comma separated string for each id. Finally, join these subqueries together to obtain the final result.

SELECT
    t1.id, t1.r1, t2.r2
FROM
(
    SELECT t.id, GROUP_CONCAT(res1agg ORDER BY res1) AS r1
    FROM
    (
        SELECT
            id,
            res1,
            CASE WHEN COUNT(*) = 1 THEN res1
                 ELSE CONCAT(CAST(COUNT(*) AS CHAR(50)), res1) END AS res1agg
        FROM yourTable
        GROUP BY id, res1
    ) t
    GROUP BY t.id
) t1
INNER JOIN
(
    SELECT t.id, GROUP_CONCAT(res2agg ORDER BY res2) AS r2
    FROM
    (
        SELECT
            id,
            res2,
            CASE WHEN COUNT(*) = 1 THEN res2
                 ELSE CONCAT(CAST(COUNT(*) AS CHAR(50)), res2) END AS res2agg
        FROM yourTable
        GROUP BY id, res2
    ) t
    GROUP BY t.id
) t2
    ON t1.id = t2.id;

Output:

Demo here:

Rextester