GROUP_CONCAT( )

This MySQL function returns non-NULL values of a group concatenated by a GROUP BY clause, separated by commas.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

GROUP_CONCAT([DISTINCT] expression[, ...]
   [ORDER BY {unsigned_integer|column|expression}
   [ASC|DESC] [,column...]]
   [SEPARATOR character])

Explanation

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, column, or 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.

Examples

As an example of this function, suppose that we wanted to know how many customers order of 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 contained a duplicate value (the 4), we're eliminating duplicates here by including the DISTINCT keyword.