Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 that are sorted by the GROUP BY clause. To sort the quantities within each field and to use a different separator, we would enter something like the following instead:

SELECT item_nbr AS Item,
GROUP_CONCAT(DISTINCT quantity
  ORDER BY quantity ASC
  SEPARATOR '|')
AS Quantities
FROM orders
WHERE item_nbr = 100
GROUP BY item_nbr;

+------+------------+
| Item | Quantities |
+------+------------+
|  100 | 4|7|8|12   | 
+------+------------+

Because the results previously contained a duplicate value (4), we’re eliminating duplicates here by including the DISTINCT keyword.