GROUP_
On this page
Concatenates all of the values passed in during aggregation.
Note
This aggregate function is not to be confused with CONCAT, which is a non-aggregate function returning a concatenation of its list of arguments.
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_ function.
Return Type
The return type is a string.group_ controls the maximum length string this function can return.
For more information on this variable, see the List of Engine Variables.
Examples
CREATE TABLE gc_products (id int, region varchar(30), ship_code varchar(5));INSERT INTO gc_products VALUES(1, 'North', 'A'),(2, 'North', 'B'),(3, 'South', 'A'),(4, 'East', 'A'),(5, 'East', 'C'),(6, 'West', 'D'),(7, 'South', 'A'),(8, 'West', 'B');
SELECT GROUP_CONCAT(ship_code) FROM gc_products;
+-------------------------+
| GROUP_CONCAT(ship_code) |
+-------------------------+
| A,B,A,A,C,D,A,B |
+-------------------------+SELECT GROUP_CONCAT(DISTINCT ship_code) AS distinct_codes FROM gc_products;
+----------------+
| distinct_codes |
+----------------+
| A,B,C,D |
+----------------+SELECT GROUP_CONCAT(DISTINCT ship_code SEPARATOR " ") AS distinct_codes FROM gc_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 |
+---------+--------------------------------------+CREATE TABLE gc_emp_info(emp_id INT, dept_code INT, salary_level INT);INSERT INTO gc_emp_info VALUES(298, 401, 5), (341, 202, 2), (491, 111, 1);
SELECT GROUP_CONCAT(CONCAT(emp_id, '-', dept_code, '-', salary_level) ORDER BY emp_id DESC, dept_code ASC) FROM gc_emp_info;
+----------------------------------------------------------------+
| GROUP_CONCAT(CONCAT(emp_id, '-', dept_code, '-', salary_level) |
| ORDER BY emp_id DESC, dept_code ASC) |
| GROUP_CONCAT(first_name, " ", last_name) |
+----------------------------------------------------------------+
| 491-111-1,341-202-2,298-401-5 |
+----------------------------------------------------------------+Related Topics
-
JSON_
AGG can be used for similar tasks and also supports ORDER BY.
Last modified: June 16, 2023