Dinesh - 1 year ago 77

SQL Question

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`

`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 Source

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