MySQL GROUP_CONCAT()

Introduction to MySQL GROUP_CONCAT()

MySQL GROUP_CONCAT() function is an aggregate GROUP BY function which is responsive for concatenating data column values from several rows into one field with string values with the help of some MySQL clauses. If the rows group comprises at least a single or no-NULL column value, then the GROUP_CONCAT function returns a sequence value otherwise the result will be NULL. We use some MySQL constraints and clauses to provide options of GROUP_CONCAT() function which are defined as follows:

Distinct: It is applied to return the unique string values in the group before the function concatenates the values. It removes the repeated values from the result rows.

Order By: By default, the sorted order of the result from the function is ascending but we can use this clause to categorize the group data either in ascending or descending order.

Separator: To make the string value separate, we can use the default operator as comma(,) or look for a different literal value.

Syntax

The syntax for GROUP_CONCAT() function is defined with the below statement:

SELECT Column1,Column2, …,ColumnNGROUP_CONCAT([DISTINCT] ColumnName1 [ORDER BY {ASC or, DESC}] [SEPARATOR {comma or any literal value}]) FROM TableName GROUP BY ColumnName2;

Let us discuss the terms used in the above context:

• Column1,…CoulmnN: Denotes the name of the columns where we apply the function in the table.
• ColumnName1: Defines the column values which will be concatenated into one row for every group.
• ColumnName2: Defines the column where the GROUP BY clause is used for grouping the results to be fetched.
• TableName: The name of the database table where we use the GROUP_CONCAT() function.

How does MySQL GROUP_CONCAT() function work?

• The MySQL GROUP_CONCAT() function provides the result from a table that contains a row including a group of string values separated by comma or other given value which is helpful to fetch the records according to a grouping process and collect the data for evaluation.
• In MySQL, the result generated by the MySQL CONCAT() function is limited to a maximum length of 1024 which is defined by the system variable named group_concat_max_len. But this variable value can be changed using the succeeding SET command at runtime:

SET [GLOBAL | SESSION] group_concat_max_len = LengthValue ;

LengthValue denotes the variable value to exceed the length of the result produced by the CONCATE() function.

We can various applications of MySQL CONCAT() function, some are mentioned below:

• We can create a comma-separated user’s roles such as Admin, Editor, Author or Subscriber, etc.
• Can display the list of hobbies of different users separated by comma like Designer, Painter, etc.
• It is also mostly applied for generating tags for articles, blog posts, guest posts or products, etc. such as MySQL coding, Learn MySQL, MySQL clauses, etc.

Examples of MySQL GROUP_CONCAT()

Let us try out some examples to understand the concept of the MySQL CONCATE() function. For this let us first create a table as a sample one to work with and insert some records into it.

Creating a table:

CREATE TABLE Books (BookID int NOT NULL, BookName varchar(255) NOT NULL, Language varchar(255), Price int, PRIMARY KEY (BookID) );

Entering records into the table using the following statement:

INSERT INTO Books (BookID, BookName, Language, Price) VALUES
('101', 'Algebraic Maths', 'English', '2000'),
('102', 'Networking', 'English', '5000'),
('103', 'Nanoscience', 'Hindi', '3000'),
('104', 'Web Design', 'English', '2000'),
('105', 'Nuclear Science', 'English', '2100'),
('106', 'Nuclear Science', 'Hindi', '1980'),
('107', 'Electrical Engineering', 'Hindi', '1500'),
('108', 'Manufacturing', 'Hindi', '1800');

Displaying the table:

SELECT * FROM Books;

Output:

Now let us start with these examples:

Example #1 – Simple MySQL_CONCAT() function

Let us create the statement which returns a list of books separated by a comma in each group of language.

SELECT Language, GROUP_CONCAT(BookName) FROM Books GROUP BY Language;

Output:

Example #2 – MySQL CONCAT() function with DISTINCT clause

Here, the MySQL query returns unique string values as grouped by Language removing the duplicate ones if present for each group in the column values using GROUP_CONCAT() function.

SELECT Language, GROUP_CONCAT(DISTINCT BookName) FROM Books GROUP BY Language;

Output:

Example #3 – MySQL CONCAT() function with ORDER BY Clause

In this illustration, we will get the list of distinct BookName values from the MySQL statement differentiated by commas. As you see in the result the fetched rows are sorted in ascending order using the ORDER BY clause, you can also use DESC sorting.

SELECT Language, GROUP_CONCAT(DISTINCT BookName) FROM Books GROUP BY Language ORDER BY  GROUP_CONCAT(DISTINCT BookName) ASC;

Output:

Example #4 – MySQL CONCAT() function with Separator

In this example, the MySQL CONCAT() function will return the list series of BookName grouped by Languagewith distinct string values but separated by adding a specific separator (‘’) space. We can also use other literal values like semicolon(;), colon(:), etc. The result rows will be displayed in descending order as it is added at the end of the SELECT MySQL query below in the option.

SELECT Language, GROUP_CONCAT(DISTINCT BookName ORDER BY BookName DESC SEPARATOR ' ') FROM Books GROUP BY Language;

Output:

Example #5 – MySQL CONCAT() function: some mistakes

Since, the result returned by the GROUP_CONCAT function is a single string, not a list of values; it concatenates all the values in one list denoted as a single string. It is suggested not to apply GROUP_CONCAT() function for operator IN in MySQL i.e. within an inner query.

Like for example, the result of values: 21,2 and 23 when applied GROUP_CONCAT() function will be as a string: ’21,22,23’.

IN operator receives a series of values but not a string including the list of column values. Therefore if you provide this type of result using IN operator in MySQL, then you did not get an expected result and the query below will not work as shown in the output.

SELECT BookID, BookName FROM Books WHEREBookID IN GROUP_CONCAT(BookID);

Output:

Even we need to use the ORDER BY clause properly in the GROUP_CONCAT() function query statement so that we get the desired output. As we know this is an aggregate function so we should use ORDER BY inside the function but not in the SELECT statement part.

The succeeding query represents the incorrect usage of such a mistake:

SELECT GROUP_CONCATE(DISTINCT BookName SEPARATOR ' ; ') FROM Books ORDER BY BookName;

Output:

Conclusion

• The MySQL CONCAT() function on execution results in binary or non-binary string values always which depends on the particular arguments or parameters provided in the function during code definition.
• This function can be beneficial to return the column values displaying in a single field using ORDER BY and GROUP BY clauses respectively to get the desired result from column table values.

Recommended Articles

This is a guide to MySQL GROUP_CONCAT(). Here we discuss How do MySQL GROUP_CONCAT() function work with respective examples for better understanding. You may also look at the following articles to learn more –

1. Unique Key in MySQL
2. MySQL Self Join
3. NOT in MySQL
4. Cassandra vs MySQL