> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/sql-functions/aggregate-functions/group_concat.md).

# GROUP\_CONCAT

Complete GROUP\_CONCAT reference for MariaDB. Complete function guide with syntax, parameters, return values, and usage examples for production use.

## Syntax

```bnf
GROUP_CONCAT(expr)
```

## Description

This function returns a string result with the concatenated non-NULL values from a group. If any expr in GROUP\_CONCAT evaluates to NULL, that tuple is not present in the list returned by GROUP\_CONCAT.

It returns NULL if all arguments are NULL, or there are no matching rows.

The maximum returned length in bytes is determined by the [group\_concat\_max\_len](/docs/server/server-management/variables-and-modes/server-system-variables.md#group_concat_max_len) server system variable, which defaults to 1M.

If group\_concat\_max\_len <= 512, the return type is [VARBINARY](/docs/server/reference/data-types/string-data-types/varbinary.md) or [VARCHAR](/docs/server/reference/data-types/string-data-types/varchar.md); otherwise, the return type is the smallest [BLOB](/docs/server/reference/data-types/string-data-types/blob.md) or [TEXT](/docs/server/reference/data-types/string-data-types/text.md) type that can hold a result of that length. With the default `group_concat_max_len`, that is `MEDIUMBLOB` or `MEDIUMTEXT`. The choice between binary or non-binary types depends on the input.

If the result is longer than `group_concat_max_len`, it is truncated to that length, and a warning is issued:

```
Warning (Code 1260): Row 2 was cut by group_concat()
```

The full syntax is as follows:

```sql
GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val]
             [LIMIT {[offset,] row_count | row_count OFFSET offset}])
```

`DISTINCT` eliminates duplicate values from the output string.

[ORDER BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md) determines the order of returned values.

`SEPARATOR` specifies a separator between the values. The default separator is a comma (`,`). It is possible to avoid using a separator by specifying an empty string.

### LIMIT

The [LIMIT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit.md) clause can be used with `GROUP_CONCAT`.

### Memory Usage with ORDER BY

{% hint style="info" %}
This behavior applies from MariaDB 13.2.
{% endhint %}

With `ORDER BY`, the rows of each group are sorted in memory. The memory used for sorting is limited to the larger of these two values:

* [group\_concat\_max\_len](/docs/server/server-management/variables-and-modes/server-system-variables.md#group_concat_max_len), or [max\_allowed\_packet](/docs/server/server-management/variables-and-modes/server-system-variables.md#max_allowed_packet) if that is smaller
* The smaller of [tmp\_memory\_table\_size](/docs/server/server-management/variables-and-modes/server-system-variables.md#tmp_memory_table_size) and [max\_heap\_table\_size](/docs/server/server-management/variables-and-modes/server-system-variables.md#max_heap_table_size)

With the default settings, the limit is 16 MB. The limit is never set so low that the sort cannot hold a few rows. Values of `BLOB` and `TEXT` arguments are not counted toward it.

When the sort reaches two thirds of the limit, the server discards the rows that come after the end of the result in `ORDER BY` order and can therefore never be returned. If the rows that belong to the result do not fit into a third of the limit, some of them are left out of the result, and the same warning as for a truncated result is issued.

Before MariaDB 13.2, the rows were only discarded once the strings being sorted added up to about 1024 times `group_concat_max_len`, which is 1 GB with the default settings, so a large group could use a lot of memory.

[JSON\_ARRAYAGG()](/docs/server/reference/sql-functions/aggregate-functions/json_arrayagg.md) is implemented on top of `GROUP_CONCAT()`, so `JSON_ARRAYAGG()` with `ORDER BY` has the same memory limit and issues the same warning.

## Examples

```sql
SELECT student_name,
       GROUP_CONCAT(test_score)
       FROM student
       GROUP BY student_name;
```

Get a readable list of MariaDB users from the [mysql.user](/docs/server/reference/system-tables/the-mysql-database-tables/mysql-user-table.md) table:

```sql
SELECT GROUP_CONCAT(DISTINCT User ORDER BY User SEPARATOR '\n')
   FROM mysql.user;
```

In the former example, `DISTINCT` is used because the same user may occur more than once. The new line (`\n`) used as a `SEPARATOR` makes the results easier to read.

Get a readable list of hosts from which each user can connect:

```sql
SELECT User, GROUP_CONCAT(Host ORDER BY Host SEPARATOR ', ') 
   FROM mysql.user GROUP BY User ORDER BY User;
```

The former example shows the difference between the `GROUP_CONCAT`'s [ORDER BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md) (which sorts the concatenated hosts), and the `SELECT`'s [ORDER BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md) (which sorts the rows).

[LIMIT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/limit.md) can be used with `GROUP_CONCAT`, so, for example, given the following table:

```sql
CREATE TABLE d (dd DATE, cc INT);

INSERT INTO d VALUES ('2017-01-01',1);
INSERT INTO d VALUES ('2017-01-02',2);
INSERT INTO d VALUES ('2017-01-04',3);
```

the following query:

```sql
SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) 
ORDER BY cc DESC),",",1) FROM d;
+----------------------------------------------------------------------------+
| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |
+----------------------------------------------------------------------------+
| 2017-01-04:3                                                               |
+----------------------------------------------------------------------------+
```

can be more simply rewritten as:

```sql
SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;
+-------------------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |
+-------------------------------------------------------------+
| 2017-01-04:3                                                |
+-------------------------------------------------------------+
```

NULLS:

```sql
CREATE OR REPLACE TABLE t1 (a int, b char);

INSERT INTO t1 VALUES (1, 'a'), (2, NULL);

SELECT GROUP_CONCAT(a, b) FROM t1;
+--------------------+
| GROUP_CONCAT(a, b) |
+--------------------+
| 1a                 |
+--------------------+
```

## See Also

* [CONCAT()](/docs/server/reference/sql-functions/string-functions/concat.md)
* [CONCAT\_WS()](/docs/server/reference/sql-functions/string-functions/concat_ws.md)
* [SELECT](/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md)
* [ORDER BY](/docs/server/reference/sql-statements/data-manipulation/selecting-data/order-by.md)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}