How do I group two column but return all three column value from single table

Question

I have single table name test with field1, field2 and field3

Field1     Field2    Field3
a             x        10
a             x        20 
b             g        30 
b             g        40  
c             u        50
c             u        60

I want to see group Field1, Field2, (NOT Field3) and return all value Field3

[format](http://meta.stackexchange.com/questions/24793/how-to-use-formatting-in-comments) question properly... — Fahim Parkar, Feb 11 '12 at 05:44
could you also post output that you want in question too?? I have no clue what you want... `I want to see group Field1, Field2,( NOT Field3) and return all value Field3 ??????????` — Fahim Parkar, Feb 11 '12 at 05:46
Can we write sql to return all value in field3 having group by field1 and field2. I hope make more sense to you — user1203397, Feb 11 '12 at 05:51
output please in question?? we can do but we are not getting what you want... — Fahim Parkar, Feb 11 '12 at 05:55
Please state what you think the results should be if you ran the query on the sample data you provided. — Jeremy Wiggins, Feb 11 '12 at 05:57
Ok like this Select field1, field2, field3 from tableone group by field1, field2 and it should return field1 group a,b,c and field2 group x,g,u and field3 return 10,20,30,40,50,60 — user1203397, Feb 11 '12 at 06:00
Do you require comma seperated values in column Field3 of group field1 and field2? — Somnath Muluk, Feb 11 '12 at 06:03
See this link for values in one column x row:[here](http://stackoverflow.com/questions/3371914/how-to-get-multiple-column-data-in-a-comma-separated-string) — Somnath Muluk, Feb 11 '12 at 06:30
I need to see value return in column for field1, field2 and field3. Can we possible to do it or impossible? — user1203397, Feb 11 '12 at 06:39

John Woo · Accepted Answer · 2012-02-11T07:31:02.153

0

Your question is very unclear. If i understand your question correctly, this is what you want right? You didn't mention to SUM up the values in Field3 or MAX, MIN or etc.

SELECT Field1, Field2, Group_Concat(Field3)
FROM tableName
GROUP BY Field1

Result:

Field1     Field2    Field3
a             x        10, 20 
b             g        30, 40
c             u        50, 60

UPDATE

as i read the comment, you want this query:

SELECT group_concat(Distinct Field1),
       GROUP_CONCAT(DISTINCT Field2),
       GROUP_CONCAT(Field3)
FROM tableName;

will Result:

Field1     Field2    Field3
a,b,c      x,g,u       10, 20 , 30, 40, 50, 60

edited Feb 11 '12 at 07:31

answered Feb 11 '12 at 07:25

John Woo

258,903
69
498
492

Try this but error on sql 2008 Msg 195, Level 15, State 10, Line 2 'group_concat' is not a recognized aggregate function. Please help agian – user1203397 Feb 13 '12 at 19:31
@user1203397 what server are you using? `group_concat` is available on MySQL and SQLite, other than that you have to create a function same with functionality of `Group_Concat` – John Woo Feb 14 '12 at 01:03

How do I group two column but return all three column value from single table

1 Answers1