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 ?

Tim Biegeleisen · Accepted Answer · 2017-04-28T06:40:35.830

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

1+ good one. I have also thought to make sub query. But for me it take times — krishn Patel, Apr 28 '17 at 06:33
can u explain how the cast functinality works for this code: — goonerboi, May 02 '17 at 04:47

India.Rocket · Answer 2 · 2017-04-28T08:08:26.527

0

Just added 2 more conditions in your query without using more inner queries.

Try this:-

input:-

CREATE TABLE Table1 (id INT, res1 varchar(20), res2 varchar(20));
insert into Table1  values(1, 'a', 'f');
insert into Table1  values(1, 'b', 'f');
insert into Table1  values(1, 'b', 'f');
insert into Table1  values(1, 'c', 'f');
insert into Table1  values(2, 'e', 'g');
insert into Table1  values(2, 'e', 'g');
insert into Table1  values(2, 'e', 'g');
insert into Table1  values(2, 'f', 'g');

Query:-


 Select t.id,group_concat(case when cnt = 1 then res1 else concat(cnt, ' ', res1) end) as r1,
 case when id=1 then trim(concat(sum(case when id = 1 then cnt end),' ',res2)) 
 else trim(concat(sum(case when id = 2 then cnt end),' ',res2)) end as r2
 from
 (
  select id, res1,res2,count(*) as cnt
  from table1 a
  group by id, res1,res2
 ) t
 group by t.id

My Output:-
    id  r1        r2
    1   a,c,2 b  4 f
    2   f,3 e    4 g

Let me know if you have any questions

edited Apr 28 '17 at 08:08

answered Apr 28 '17 at 07:03

India.Rocket

1,225
1
8
11

@goonemike Can you try this? – India.Rocket Apr 28 '17 at 07:04
..Its showing 0 value before the character for all the rows in r2 – goonerboi Apr 28 '17 at 07:19
@gonnermike It was working for me. I have put the complete code with input and output now. I have also removed the else 0 condition from the code which was not required and also added trim to remove spaces if that's what you are getting. Can you try again – India.Rocket Apr 28 '17 at 08:06
@goonernike I have tried in sql fiddle also, this is the below given link . Its working over there also. Check this link out http://sqlfiddle.com/#!9/2dbd82/1/0 – India.Rocket Apr 28 '17 at 09:23

modify the values achieved through group_Concat in MySql?

2 Answers2

Rextester