Search code examples
mysqlgroup-bydistinctgroup-concat

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

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