Aggregate function. Concatenates all of the values passed in during aggregation.

Syntax

GROUP_CONCAT( [DISTINCT]
           expression, [ expression, [expression ...]]
           [ORDER BY expression [ASC|DESC]]
           [SEPARATOR string] )

Arguments

• DISTINCT: optionally filter the result set for only unique sets of expressions
• expression: any set of expressions. This may be a column name, the result of another function, or a math operation.
• ORDER BY: optionally sort the result set of the GROUP_CONCAT values in ascending or descending order. By default, the result is sorted in ascending order.
• SEPARATOR string: by default, the separator character is the comma “,". This argument overrides the default.

Note: You cannot use the DISTINCT keyword with the ORDER BY clause in a GROUP_CONCAT function.

Return Type

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.

Examples

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                                  |
+---------+--------------------------------------+

Was this Article Helpful?

Thanks For Your Feedback!