title: MySQL GROUP_CONCAT excluding group value
body: <p>I am trying to do a group concat for the table below</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I would like get the group concat of those elements which do not belong to the group when we use <code>GROUP BY</code> and also sum the numbers (in a similar way). For example, the output I am trying to obtain is</p>
  
  <pre><code>l   grps   sum(num)
  A   B,C       4
  B   C        21
  C   A         3
  </code></pre>
  
  <p>I am currently getting the output as below</p>
  
  <pre><code>l   grps   sum(num)
  A   B,C,A     9
  B   C        21
  C   A,C       6
  </code></pre>
  
  <p>I use the query below </p>
  
  <pre><code>SELECT l, group_concat(distinct r), sum(num)
  from groups
  group by l;
  </code></pre>
  
  <p>The SQL fiddle is <a href="http://sqlfiddle.com/#!9/4dda59/4" rel="nofollow">here</a></p>