Dinesh Dinesh - 4 months ago 29
SQL Question

MySQL GROUP_CONCAT excluding group value

I am trying to do a group concat for the table below

l r num
A B 1
A C 3
A A 5
B C 5
B C 7
B C 9
C A 1
C A 2
C C 3


I would like get the group concat of those elements which do not belong to the group when we use
GROUP BY
and also sum the numbers (in a similar way). For example, the output I am trying to obtain is

l grps sum(num)
A B,C 4
B C 21
C A 3


I am currently getting the output as below

l grps sum(num)
A B,C,A 9
B C 21
C A,C 6


I use the query below

SELECT l, group_concat(distinct r), sum(num)
from groups
group by l;


The SQL fiddle is here

Answer
SELECT l, GROUP_CONCAT(DISTINCT r), SUM(num)
FROM groups
WHERE l <> r
GROUP BY l;