0

I'm attempting to group this data by name so that instead of current query output giving me:

Name                       Number
Nice guy                   1
Nice guy                   2
Nice guy                   4
Nice guy                   5
Nice guy                   6
Nice guy                   7
Nice guy                   8
Nice guy                   9
Nice guy                   10
Nice guy                   11
Nice guy                   12
Frank                      3
Frank                      4

I would get this:

Name       Number
Nice guy   1,2,4,...
Frank      3,4

Here is my current query:

select distinct name, number
from patterns,numbers,people
where patterns.index=numbers.index
AND patterns.id=people.id
order by name, charge;

What I have tried is this, but it fails:

select distinct name, number
from patterns,numbers,people
where patterns.index=numbers.index
AND patterns.id=people.id
group by name
order by name, number;

Any help would be greatly appreciated!

peterm
  • 91,357
  • 15
  • 148
  • 157
thehandyman
  • 919
  • 2
  • 8
  • 17

1 Answers1

3

UPDATED: Try it this way

SELECT name, WM_CONCAT(number) number
  FROM
(
  SELECT DISTINCT name, number 
    FROM patterns t JOIN numbers n
      ON t.index = n.index JOIN people p
      ON t.id = p.id
) q
 GROUP BY name
 ORDER BY name
peterm
  • 91,357
  • 15
  • 148
  • 157
  • This works! However, I now get duplicate values with the concatenation. Meaning for Nice guy I get 1,1,2,... instead of what I was getting before with 1,2,... Is there any way to remove the duplicate values from this? – thehandyman Dec 09 '13 at 05:35
  • thanks, I just got this on my own but I appreciate it nonetheless! hadn't seen that function before. Much appreciated! – thehandyman Dec 09 '13 at 05:56