Change values reached with group_Concat in MySql?
I have the following table [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 am getting the following after executing 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 works fine, but the res2 column duplicates the res1 column. Basically I want to print the value of how many times the 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 this?
source to share
The way I approach this is to do two accumulators / aggregations using two separate subqueries for the columns res1
and res2
. The first aggregation exceeds id
and res1
/ or res2
and gets counts for each letter or word. Then, aggregate again, this time just over id
, to get a comma-delimited string for each id
. Finally, concatenate these subqueries together to get 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
source to share
Just added 2 more terms to your query without using any more internal 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.
source to share