How to retrieve distinct values from concat field in a group by query

Hi
Assuming the following rows in my table:
ID , Field1, Field2, ConcatField
1  , "test",    3,    "a,b"
2  , "tttt",     2,    "a,c,d"
3  , "test",   8,    "a"
4,  "test",   1,    "d"

Open in new window


I would like to run a query grouped by field1 and showing count(ID), sum(field2) and a field with all values in the concatfield, i.e
Field1, Count, Sum, ConcatField
"test",   3,  12,   "a,b,d"
"tttt",  1,  2,    "a,c,d"

Open in new window


How can i do that?
Thanks
Jaime
LVL 2
GreatSolutionsC.I.OAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Use GROUP_CONCAT(), e.g.
SELECT 
  Field1,
  COUNT(*)
  SUM(Field2)
  GROUP_CONCAT(ConcatField)
FROM your_table
GROUP BY Field1;

Open in new window

0
 
GreatSolutionsC.I.OAuthor Commented:
Many thanks for the fast response!
It gave me duplicates inside the field, but group_concat ( distinct ConcatField ) did the trick!

Jaime
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.