Name
GROUP_CONCAT()
Synopsis
GROUP_CONCAT([DISTINCT]expression
[, ...] [ORDER BY {unsigned_integer|column|expression
} [ASC|DESC] [,column
...]] [SEPARATORcharacter
])
This function returns non-NULL values of a group concatenated
by a GROUP BY
clause, separated by commas. The
parameters for this function are included in the parentheses,
separated by spaces, not commas. The function returns NULL if the
group doesn’t contain non-NULL
values.
Duplicates are omitted with the DISTINCT
keyword. The ORDER BY
clause instructs the function to sort values before
concatenating them. Ordering may be based on an unsigned integer
value, a column, or an expression. The sort order can be set to
ascending with the ASC
keyword (default), or to
descending with DESC
. To use a different separator
from a comma, use the SEPARATOR
keyword followed by the
preferred separator.
The value of the system variable
group_concat_max_len
limits the number of elements
returned. Its default is 1024. Use the SET
statement to change the value. This function is available as of
version 4.1 of MySQL.
As an example of this function, suppose that we wanted to know how many customers order a particular item. We could enter an SQL statement like this:
SELECT item_nbr AS Item, GROUP_CONCAT(quantity) AS Quantities FROM orders WHERE item_nbr = 100 GROUP BY item_nbr; +------+------------+ | Item | Quantities | +------+------------+ | 100 | 7,12,4,8,4 | +------+------------+
Notice that the quantities aren’t sorted—it’s the item numbers ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.