MySQL GROUP_CONCAT Function
summary: in this tutorial, you will learn how to use the MySQL GROUP_CONCAT()
function to concatenate strings from a group with various options.
Introduction to MySQL GROUP_CONCAT()
function
The MySQL GROUP_CONCAT()
the function is an aggregate function that concatenates strings from a group into a single string with various options.
The following shows the syntax of the GROUP_CONCAT()
function:
GROUP_CONCAT( DISTINCT expression ORDER BY expression SEPARATOR sep );
The following example demonstrates how the GROUP_CONCAT()
function works.
CREATE TABLE t (
v CHAR
);
INSERT INTO t(v) VALUES('A'),('B'),('C'),('B');
SELECT
GROUP_CONCAT(DISTINCT v
ORDER BY v ASC
SEPARATOR ';')
FROM
t;
The DISTINCT
the clause allows you to eliminate duplicate values in the group before concatenating them.
The ORDER BY
the 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 descending order, you need to specify explicitly the DESC
option.
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.
The GROUP_CONCAT
function ignores NULL
values. It returns NULL
if there was no matching row found or all arguments are NULL
values.
The GROUP_CONCAT
the function returns a binary or non-binary string, which depends on the arguments. by default, the maximum length of the return string is 1024. In case 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.
MySQL GROUP_CONCAT()
function examples
Let’s take a look at the customers
table in the sample database.
To get all countries where customers locate 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
a clause like the following query:
SELECT
GROUP_CONCAT(DISTINCT country)
FROM
customers;
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;
To change the default separator of the returned string from a comma (,) to a semi-colon (;), you use the SEPARATOR
a clause like the following query:
SELECT
GROUP_CONCAT(DISTINCT country
ORDER BY country
SEPARATOR ';')
FROM
customers;
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 being 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;
The result set is much easier to read.
Using MySQL GROUP_CONCAT()
with CONCAT_WS()
function example
Sometimes, the GROUP_CONCAT
the function can be combined with the CONCAT_WS function 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;
Note that GROUP_CONCAT()
function concatenates string values in different rows while the CONCAT_WS()
or CONCAT()
function concatenates two or more string values in different columns.
MySQL GROUP_CONCAT
function: common mistakes
The GROUP_CONCAT()
the function returns a single string, not a list of values. It means you cannot use the result of the GROUP_CONCAT()
function for IN operator e.g., within a subquery.
For example, the GROUP_CONCAT()
the function returns the result of values:1
2
, and 3
as the ‘1,2,3’ string.
If you supply this result to the IN
operator, the query is not working. Therefore, the query may not return any result. For example, the following query will not work as desired.
Because the IN
the operator accepts a list of values e.g., (1,2,3) not a string that consists of a list of values (‘1,2,3’). As a result, the following query will not work as expected.
SELECT
id, name
FROM
table_name
WHERE
id IN GROUP_CONCAT(id);
Because the GROUP_CONCAT
the function is an aggregate function, to sort the values, you must use the ORDER BY
clause inside the function, not in the ORDER BY
in the SELECT statement.
The following example demonstrates the incorrect usage of the ORDER BY
a clause in the context of using the GROUP_CONCAT
function:
SELECT
GROUP_CONCAT(DISTINCT country
SEPARATOR ';')
FROM
customers
ORDER BY country;
The SELECT
clause returns one string value so the ORDER BY
the clause does not take any effect on this statement.
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.
0 Comments
CAN FEEDBACK
Emoji