MySQL GROUP_CONCAT() Function – 5 Easy Examples

MySQL provides us with a wide range of aggregate functions that make our life easier. The MySQL GROUP_CONCAT() is one of those aggregate functions pretty similar to MySQL CONCAT() aggregate function. Jowever, it is built for a slightly different purpose. In this tutorial, we will study the MySQL GROUP_CONCAT() function and learn how to use it practically with multiple examples.

What is MySQL GROUP_CONCAT() Function?

The GROUP_CONCAT() is an aggregate function in MySQL that is used to return concatenated string values if at least one of the values in the group is Non-NULL. In simple terms, GROUP_CONCAT() function concatenates the values from multiple rows into a single string and returns it.

There is a broad scope of using a GROUP_CONCAT() aggregate function. You can write a simple statement that consists of a GROUP_CONCAT() function or a statement with a combination of aggregate functions such as ORDER BY, GROUP BY, and alike.

Let’s start from syntax and then dive into a practical approach so that you can understand the GROUP_CONCAT() function quickly.

Syntax of MySQL GROUP_CONCAT() Function

The GROUP_CONCAT() function has multiple optional parameters for different possible outcomes. Check syntax below-

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])Code language: SQL (Structured Query Language) (sql)

Where,

• DISTINCT is used to avoid repeated values
• ORDER BY is used to sort the values in either ascending or descending order
• SEPERATOR is used to specify a seperator between two values in a resulting a string. By default, values in string are seperated by comma (,) operator.

Note- The square brackets ( [] ) are used to represent optional parameters.

Now let’s see some examples of the GROUP_CONCAT() function. We will start from a simple example and then head towards a complex one.

Examples of MySQL GROUP_CONCAT() Function

First, we will create a table of three attributes: id, subject, and student name. There can be multiple subjects related to a single student, and there are records of multiple students. We will have only one table here so that we can focus on the functionality of our topic without getting dragged away.

CREATE TABLE students(
id INT PRIMARY KEY AUTO_INCREMENT,
subject VARCHAR(10),
name VARCHAR(100)
);Code language: SQL (Structured Query Language) (sql)

The table is created. Let’s insert some values in it.

INSERT INTO students(subject,name)
VALUES("English","Yuji"),("Spanish","Yuji"),
("Hindi","Genos"),("English","John"),
("Marathi","Raj"),("Hindi","Raj"),("Spanish","Maya"),
("Hindi","Maya"),("English","Mira");Code language: SQL (Structured Query Language) (sql)

Let’s display and check if all data in the table is correctly inserted.

SELECT * FROM students;Code language: SQL (Structured Query Language) (sql)

We are all set now. Let’s try a basic example of the GROUP_CONCAT() function.

We will get all the subjects present in our table as a single string using GROUP_CONCAT() function. Check below-

1. Basic GROUP_CONCAT() Function example

SELECT GROUP_CONCAT(subject) FROM students;Code language: SQL (Structured Query Language) (sql)

Did you recognize something wrong here? Yes, the output string consists same subjects multiple times. That’s because GROUP_CONCAT() function concatenates all the rows available in the table into a single string without ignoring repetition.

For avoiding duplication, we can use the DISTINCT clause with a GROUP_CONCAT() function.

2. Using DISTINCT Clause in GROUP_CONCAT() Function

SELECT GROUP_CONCAT(DISTINCT subject) FROM students;Code language: SQL (Structured Query Language) (sql)

Now we got desired output without any subject repetition.

However, the subject names are not sorted by alphabetical order. We can arrange the subjects in either ascending or descending order.

3. Using ORDER BY Clause in GROUP_CONCAT() Function

SELECT GROUP_CONCAT(DISTINCT subject ORDER BY subject ASC) FROM students;Code language: SQL (Structured Query Language) (sql)

SELECT GROUP_CONCAT(DISTINCT subject ORDER BY subject DESC) FROM students;Code language: SQL (Structured Query Language) (sql)

As you can see, the subject names are sorted in ascending and descending order, respectively.

The subject names are separated by comma (,) by default. If we want another separator, we can specify it too.

4. Using Custom Separator in GROUP_CONCAT() Function

SELECT GROUP_CONCAT(DISTINCT subject ORDER BY subject DESC SEPARATOR '/') FROM students;Code language: SQL (Structured Query Language) (sql)

We have seen some simple examples above. Let’s take a slightly difficult example where we want to display all subjects that are related to each student. After that, we will also see an example where we will show the students’ names related to each subject, a vice-versa of the previous statement.

First, let’s write a query to display subject lists associated with each student. To achieve this, we will use the GROUP BY clause. Take a glance-

5. Combining GROUP_CONCAT() with Other Clauses

SELECT id, name, GROUP_CONCAT(subject) FROM students
GROUP BY name ORDER BY id;Code language: SQL (Structured Query Language) (sql)

Here, we have used GROUP_CONCAT() clause on the subject column and grouped the data using the GROUP BY clause, which will finally give us the above result. We have also used the ORDER BY clause to sort the output by id attribute in ascending order.

Now, take a second example. We will perform a vice-versa action of the earlier example. We will be displaying the student names that are related to each subject.

SELECT id, subject, GROUP_CONCAT(name)FROM students
GROUP BY subject ORDER BY id;Code language: SQL (Structured Query Language) (sql)

As you can see, we have received subject names in the first column and the list of students who are related to those subjects in the second column.

Note- The result is clipped to the maximum length specified by the system variable group_concat_max_len, whose default value is 1024. Although the value of max_allowed_packet binds the effective maximum length of the return value, the value can be increased higher. The following is the syntax for changing group_concat_max_len’s value at runtime, where Val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;Code language: SQL (Structured Query Language) (sql)

Conclusion

In this tutorial, we have learned what GROUP_CONCAT() aggregate function is, its syntax, its usage, and how to combine it with other aggregate functions. It’s a beneficial function provided by MySQL that makes your task easier to group up the row values without writing nested queries or logical programs. We urge you to try multiple examples of the GROUP_CONCAT() function on your own and use it in your applications. See you in the next tutorial.

References

MySQL Official documentation of GROUP_CONCAT() Function.