In this blog, you will study how to use the MySQL GROUP_CONCAT function to concatenate strings from a group with several choices.
Overview to MySQL GROUP_CONCAT function
The MySQL GROUP_CONCAT function concatenates strings from a group into a single string with several decisions.
The subsequent shows the syntax of the GROUP_CONCAT function:
The subsequent example demonstrates how the GROUP_CONCAT function works.
The DISTINCT clause lets you to remove duplicate values in the group already concatenating them.
The ORDER BY clause let’s 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.
The SEPARATOR requires 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 similar row found or all arguments are NULL values.
The GROUP_CONCAT 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 cover the maximum length by setting the group_concat_max_len system variable at SESSION or GLOBAL level.
MySQL GROUP_CONCAT examples
Let’s 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:
However, some customers located in the similar country. To remove the duplicate country’s names, you add the DISTINCT clause as the subsequent query:
It is clearer if the country’s names are in ascending order. To sort the country’s name before concatenating, you use the ORDER BY section as follows:
To change the default separator of the returned string from a comma (,) to a semi-colon (;), you use the SEPARATOR clause as the subsequent query:
Excessive! now you know how the GROUP_CONCAT function works. Let’s put it in a applied example.
Each customer has one or extra sale representatives. In other words, each sales employee oversees one or more customers. To find out who in charge of which customers, you use the inner join clause as follows:
Now, we can group the result set by the employee number and concatenate all employees that are overseeing the employee by using the GROUP_CONCAT function as follows:
The result set is much easier to read.
MySQL GROUP_CONCAT with CONCAT_WS function example
Sometimes, the GROUP_CONCAT function can be combined with the CONCAT_WS function to make a result of 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 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.
Note that GROUP_CONCAT function concatenates string values in unlike rows while the CONCAT_WS or CONCAT function concatenates two or more string values in dissimilar columns.
MySQL GROUP_CONCAT function: common mistakes
The GROUP_CONCAT 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 function returns the result of values:1 2, and 3 as the ‘1,2,3’ string.
If you stock this result to the IN operator, the query is not working. Therefore, the query may not return any result. For example, the subsequent query will not work as anticipated.
Because, the IN operator accepts a list of values e.g., (1,2,3) not a string that contains of a list of values (‘1,2,3’). As the result, the subsequent query will not work as predictable.
Since the GROUP_CONCAT function is an aggregate function, to sort the values, you must use the ORDER BY clause private the function, not in the ORDER BY in the SELECT declaration.
The next example validates the incorrect usage of the ORDER BY clause in the context of using the GROUP_CONCAT function:
The SELECT clause returns one string value so the ORDER BY clause does not take any effect in this statement.
MySQL GROUP_CONCAT applications
There are many cases where you can put on the GROUP_CONCAT function to crop useful results. The next list is some shared examples of the 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 blog, we have presented you to MySQL GROUP_CONCAT function that concatenates non-NULL values from a group of strings into a string with several choices.
Comments