MySQL Function : GROUP_CONCAT()

Concatenates values into a string

The GROUP_CONCAT() function in MySQL is an aggregate function that joins multiple row values into a single string. It's especially useful for generating summarized lists, CSV-style strings, and grouped information.

Syntax

GROUP_CONCAT(
  [DISTINCT] expression
  [ORDER BY expression [ASC|DESC]]
  [SEPARATOR 'separator']
)

Key Options:

• DISTINCT: Removes duplicates before concatenating.
• ORDER BY: Sorts the values before joining.
• SEPARATOR: Defines the delimiter between values (default is comma).

NOTE

1. The DISTINCT clause allows you to eliminate duplicate values in the group before concatenating them.
2. The ORDER BY clause allows you to sort the values in ascending or descending order before concatenating. By default, it sorts the values in ascending order. If you want to sort the values in the descending order, you need to specify explicitly the DESC option.
3. The SEPARATOR specifies a literal value inserted between values in the group. If you do not specify a separator, the GROUP_CONCAT function uses a comma (,) as the default separator.
4. The GROUP_CONCAT function ignores NULL values. It returns NULL if there are no matching rows or all arguments are NULL values.
5. The GROUP_CONCAT function returns a binary or non-binary string, which depends on the arguments.
6. By default, the maximum length of the return string is 1024. If you need more than this, you can extend the maximum length by setting the group_concat_max_len system variable at SESSION or GLOBAL level.

Reference Table: customers

Summary Table

NOTE 1

MySQL GROUP_CONCAT() function examples

Let’s take a look at the customers table in the sample database:

To get all countries where customers are located as a comma-separated string, you use the GROUP_CONCAT() function as follows:

SELECT
    GROUP_CONCAT(country)
FROM
    customers;

However, some customers are located in the same country. To remove the duplicate country’s names, you add the DISTINCT clause as the following query:

SELECT 
    GROUP_CONCAT(DISTINCT country)
FROM
    customers;

Australia,Austria,Belgium,Canada,Denmark,Finland,France,Germany,Hong Kong,Ireland,Israel,Italy,Japan,Netherlands,New Zealand,Norway,Norway  ,Philippines,Poland,Portugal,Russia,Singapore,South Africa,Spain,Sweden,Switzerland,UK,USA 

It is more readable if the country’s names are in ascending order. To sort the country’s name before concatenating, you use the ORDER BY clause as follows:

SELECT 
    GROUP_CONCAT(DISTINCT country ORDER BY country)
FROM
    customers;

Australia,Austria,Belgium,Canada,Denmark,Finland,France,Germany,Hong Kong,Ireland,Israel,Italy,Japan,Netherlands,New Zealand,Norway,Norway  ,Philippines,Poland,Portugal,Russia,Singapore,South Africa,Spain,Sweden,Switzerland,UK,USA

To change the default separator of the returned string from a comma (,) to a semi-colon (—), you use the SEPARATOR clause as the following query:

select 
group_concat(distinct country order by country separator '__') from customers

Australia__Austria__Belgium__Canada__Denmark__Finland__France__Germany__Hong Kong__Ireland__Israel__Italy__Japan__Netherlands__New Zealand__Norway__Norway  __Philippines__Poland__Portugal__Russia__Singapore__South Africa__Spain__Sweden__Switzerland__UK__USA

NOTE 2

Great! now you know how the GROUP_CONCAT() function works. Let’s put it in a practical example.

Each customer has one or more sales representatives. In other words, each sales employee is in charge of one or more customers. To find out who is in charge of which customers, you use the inner join clause as follows:

SELECT
    employeeNumber,
    firstname,
    lastname,
    customername
FROM
    employees
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employees.employeeNumber
ORDER BY
	firstname,
    lastname;

Now, we can group the result set by the employee number and concatenate all employees that are in charge of the employee by using the GROUP_CONCAT() function as follows:

SELECT 
    employeeNumber,
    firstName,
    lastName,
    GROUP_CONCAT(DISTINCT customername
        ORDER BY customerName)
FROM
    employees
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;

Using GROUP_CONCAT() with CONCAT_WS() function example

Sometimes, the GROUP_CONCAT function can be combined with the CONCAT_WSfunction to make the result of the query more useful.

For example, to make a list of semicolon-separated values of customers:

• First, you concatenate the last name and first name of each customer’s contact using the CONCAT_WS() function. The result is the contact’s full name.
• Then, you use the GROUP_CONCAT() function to make the list.

The following query makes a list of semicolon-separated values of customers.

SELECT
GROUP_CONCAT(
CONCAT_WS(', ', contactLastName, contactFirstName)
SEPARATOR ';')
FROM
customers;

MySQL GROUP_CONCAT() function applications

There are many cases where you can apply the GROUP_CONCAT() function to produce useful results. The following list is some common examples of using the GROUP_CONCAT() function.

• Make a comma-separated user’s roles such as ‘admin, author, editor’.
• Produce the comma-separated user’s hobbies e.g., ‘design, programming, reading’.
• Create tags for blog posts, articles, or products e.g., ‘mysql, mysql aggregate function, mysql tutorial’.

In this tutorial, you have learned how to use the MySQL GROUP_CONCAT() function to concatenate non-NULL values of a group of strings into a single string.