# GROUP\_CONCAT

Concatenates all of the values passed in during aggregation.

> **📝 Note**: This aggregate function is not to be confused with [CONCAT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/string-functions/concat.md), 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_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](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#non-sync-variables-list.md).

## Examples

```sql
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');
```

```sql
SELECT GROUP_CONCAT(ship_code) FROM gc_products;

```

```output

+-------------------------+
| GROUP_CONCAT(ship_code) |
+-------------------------+
| A,B,A,A,C,D,A,B         |
+-------------------------+

```

```sql
SELECT GROUP_CONCAT(DISTINCT ship_code) AS distinct_codes FROM gc_products;

```

```output

+----------------+
| distinct_codes |
+----------------+
| A,B,C,D        |
+----------------+

```

```sql
SELECT GROUP_CONCAT(DISTINCT ship_code SEPARATOR " ") AS distinct_codes FROM gc_products;

```

```output

+----------------+
| distinct_codes |
+----------------+
| A B C D        |
+----------------+

```

```sql
SELECT GROUP_CONCAT(first_name, " ", last_name) FROM bffs;

```

```output

+------------------------------------------+
| GROUP_CONCAT(first_name, " ", last_name) |
+------------------------------------------+
| Cecelia Cruz,Ari Floo,Skylar Rhodes      |
+------------------------------------------+

```

```sql
SELECT dept_id, GROUP_CONCAT(emp_id ORDER BY emp_id) FROM grptable GROUP_BY dept_id;

```

```output

+---------+--------------------------------------+
| dept_id | GROUP_CONCAT(emp_id ORDER BY emp_id) |
+---------+--------------------------------------+
|       1 | 100                                  |
|       5 | 500,700                              |
|       4 | 300,400                              |
|       6 | 600                                  |
+---------+--------------------------------------+

```

```sql
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);

```

```sql
SELECT GROUP_CONCAT(CONCAT(emp_id, '-', dept_code, '-', salary_level) ORDER BY emp_id DESC, dept_code ASC) FROM gc_emp_info;


```

```output

+----------------------------------------------------------------+
| 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg.md) can be used for similar tasks and also supports `ORDER BY`.

***

Modified at: June 16, 2023

Source: [/db/v9.1/reference/sql-reference/aggregate-functions/group-concat/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/group-concat/)

(An index of the documentation is available at /llms.txt)