Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
Aggregate function. Concatenates all of the values passed in during aggregation.
This aggregate function is not to be confused with CONCAT, which is a non-aggregate function returning a concatenation of its list of arguments.
GROUP_CONCAT( [DISTINCT]
expression, [ expression, [expression ...]]
[ORDER BY expression [ASC|DESC]]
[SEPARATOR string] )
Note: You cannot use the DISTINCT
keyword with the ORDER BY
clause in a GROUP_CONCAT
function.
The return type is a string. The result is made up of the concatenated non-NULL values from the given group, or NULL if there are no non-NULL values. The variable group_concat_max_len
controls the maximum length string this function can return. For more information on this variable, see the List of Engine Variables.
SELECT GROUP_CONCAT(ship_code) FROM products;
****
+-------------------------+
| GROUP_CONCAT(ship_code) |
+-------------------------+
| A,B,A,A,C,D,A,B |
+-------------------------+
SELECT GROUP_CONCAT(DISTINCT ship_code) AS distinct_codes FROM products;
****
+----------------+
| distinct_codes |
+----------------+
| A,B,C,D |
+----------------+
SELECT GROUP_CONCAT(DISTINCT ship_code SEPARATOR " ") AS distinct_codes FROM products;
****
+----------------+
| distinct_codes |
+----------------+
| A B C D |
+----------------+
SELECT GROUP_CONCAT(first_name, " ", last_name) FROM BFFs;
****
+------------------------------------------+
| GROUP_CONCAT(first_name, " ", last_name) |
+------------------------------------------+
| Cecelia Cruz,Ari Floo,Skylar Rhodes |
+------------------------------------------+
SELECT dept_id, GROUP_CONCAT(emp_id ORDER BY emp_id) FROM grptable GROUP_BY dept_id;
****
+---------+--------------------------------------+
| dept_id | GROUP_CONCAT(emp_id ORDER BY emp_id) |
+---------+--------------------------------------+
| 1 | 100 |
| 5 | 500,700 |
| 4 | 300,400 |
| 6 | 600 |
+---------+--------------------------------------+