Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

GROUP BY but get all values from other column

Tags:

sql

group-by

I''ll explain what I need to do on example. First of all, we have a simple table like this one, named table:

id | name ===+===== 1  | foo 1  | bar 1  | foobar 2  | foo 2  | bar 2  | foobar 

Now the query:

SELECT t.* FROM table t GROUP BY t.id 

Will get us result similar to this one:

id | name ===+===== 1  | foo 2  | foo 

But is it possible, to collect all values of name to have result like this?

id | name ===+================= 1  | foo, bar, foobar 2  | foo, bar, foobar 
like image 487
b4rt3kk Avatar asked Sep 20 '13 06:09

b4rt3kk


People also ask

Can we include all the columns in GROUP BY clause?

The GROUP BY clause must contain all the columns except the one which is used inside the group function.

Can we SELECT column which is not part of GROUP BY?

The direct answer is that you can't. You must select either an aggregate or something that you are grouping by.


1 Answers

Using MySQL you can use GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...]              [ORDER BY {unsigned_integer | col_name | expr}                  [ASC | DESC] [,col_name ...]]              [SEPARATOR str_val]) 

Something like

SELECT ID, GROUP_CONCAT(name) GroupedName FROM Table1 GROUP BY ID 

SQL Fiddle DEMO

like image 138
Adriaan Stander Avatar answered Oct 19 '22 15:10

Adriaan Stander