GROUP_CONCAT()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns a concatenated string formed from the arguments.
USAGE
GROUP_CONCAT([DISTINCT] value[, value] ...
[ORDER BY {unsigned_int | col | expr}
[ASC | DESC][, col] ...]
[SEPARATOR str_sep])
Argument Name | Description |
|---|---|
| If the parameters start with |
| The values to be concatenated |
| Sorts values in the result. The parameter to the |
| Specifies whether sorting is to be performed in ascending ( |
| The separator to use between concatenated values. Default: ",". |
DETAILS
GROUP_CONCAT() is an aggregate function that returns the concatenation of a set of values.
Any NULL values are ignored when joining the values.
If no value qualifies to be concatenated, which includes NULL values, the return value is NULL.
EXAMPLES
Example Schema and Data
All of the examples are based on the inventory table:
CREATE TABLE inventory (
item VARCHAR(20),
category VARCHAR(20),
count INT
);
INSERT INTO inventory VALUES
('Foo','in stock', 42), ('Foo','back order', 50),
('Bar','in stock', 10), ('Bar','back order', 60),
('Baz','in stock', 99), ('Baz','back order', 10),
('Foobie','in stock', 10), ('Barby','back order', NULL),
('BarBaz','in stock', NULL), ('BarBaz','back order', NULL);
Default Behavior
The default behavior for GROUP_CONCAT() is applied when DISTINCT clause is not specified. In this example, all values are used:
SELECT GROUP_CONCAT(item ORDER BY item ASC SEPARATOR ' ') AS GROUP_CONCAT_item
FROM inventory;
+----------------------------------------------------+
| GROUP_CONCAT_item |
+----------------------------------------------------+
| Bar Bar BarBaz BarBaz Barby Baz Baz Foo Foo Foobie |
+----------------------------------------------------+
DISTINCT Clause
Using GROUP_CONCAT() with the DISTINCT clause ignores duplicate values, so each value is added only once when concatenating. In this example, all distinct item count values are used:
SELECT GROUP_CONCAT(DISTINCT item ORDER BY item SEPARATOR ' ') AS GROUP_CONCAT_item
FROM inventory;
+---------------------------------+
| GROUP_CONCAT_item |
+---------------------------------+
| Bar BarBaz Barby Baz Foo Foobie |
+---------------------------------+
With GROUP BY
Using GROUP_CONCAT() with GROUP BY creates a separate concatenation for each group. In this example, GROUP BY category creates a separate concatenation for each unique category:
SELECT category, GROUP_CONCAT(item)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+---------------------------+
| category | GROUP_CONCAT(item) |
+------------+---------------------------+
| back order | Foo,Bar,Baz,Barby,BarBaz |
| in stock | Foo,Bar,Baz,Foobie,BarBaz |
+------------+---------------------------+
With NULL Values
When GROUP_CONCAT() acts on NULL values, those values are not included. In this example, some rows in the data set contain NULL values in the count column:
SELECT *
FROM inventory
WHERE item IN ('Foobie', 'Barby')
ORDER BY count;
+--------+------------+-------+
| item | category | count |
+--------+------------+-------+
| Barby | back order | NULL |
| Foobie | in stock | 10 |
+--------+------------+-------+
The following example computes a concatenation of the count column for the Foobie and Barby items:
SELECT GROUP_CONCAT(count)
FROM inventory
WHERE item IN ('Foobie', 'Barby');
+---------------------+
| GROUP_CONCAT(count) |
+---------------------+
| 10 |
+---------------------+
With Only NULL Values
When GROUP_CONCAT() is acting only on NULL values, GROUP_CONCAT() returns NULL. In this example, all rows in the data set contain NULL values in the count column:
SELECT *
FROM inventory
WHERE item = 'BarBaz'
ORDER BY count;
+--------+------------+-------+
| item | category | count |
+--------+------------+-------+
| BarBaz | in stock | NULL |
| BarBaz | back order | NULL |
+--------+------------+-------+
The following example performs a concatenation of the count column for the BarBaz item:
SELECT GROUP_CONCAT(count)
FROM inventory
WHERE item = 'BarBaz';
+---------------------+
| GROUP_CONCAT(count) |
+---------------------+
| NULL |
+---------------------+