intro
Let's learn everything you need about the MySQL GROUP_CONCAT function to master data aggregation in groups of records.
String concatenation is a common technique for aggregating data from multiple records. It involves taking one or more strings from a set of records and combining them into a single cohesive string. Due to the frequency of this operation, databases offer specialized functions to handle it efficiently. Enter the MySQL GROUP_CONCAT function!
In this article, you will see what the GROUP_CONCAT function is, how it works, and when and how to use it with real-world examples.
Let’s get started!
What Is the GROUP_CONCAT MySQL Function?
In MySQL, GROUP_CONCAT is an aggregate function that concatenates values from multiple rows into a single string. Specifically, this function returns a string containing the concatenated non-NULL values from a set of rows.
Like most SQL aggregate functions, GROUP_CONCAT is used alongside GROUP BY to merge data from each group. However, it can also be useful in scenarios that do not involve a GROUP BY clause but still require data aggregation — I’ll provide you with a couple of such scenarios later in the article.
MySQL GROUP_CONCAT: Syntax and Main Aspects
This is the syntax of GROUP_CONCAT in MySQL:
1
GROUP_CONCAT([DISTINCT] expr_1 [, expr_2, ..., expr_n]
2
[ORDER BY {unsigned_integer | col_name | expr}
3
[ASC | DESC] [, col_name ...]]
4
[SEPARATOR str_separator])
Where:
Main Aspects
A few details that you need to know about the GROUP_CONCAT MySQL function are as follows:
MySQL GROUP_CONCAT Examples
Now that you know what the GROUP_CONCAT function is, how to use it, and how it works, you are ready to explore some basic examples.
In the examples below, we will apply GROUP_CONCAT on the following users table:
Let’s dig into the examples!
Basic Usage
If we issue a query like the one below on a set of users:
1
SELECT GROUP_CONCAT(name);
The result will be as follows:
1
John,Jane,Michael,Emily,Chris,Sarah,David,Laura,Daniel,Emma
GROUP_CONCAT concatenates the values in the name column from each row in the table, separating them with a comma (the default separator).
Now, apply the GROUP_CONCAT MySQL function to a non-string column:
1
SELECT GROUP_CONCAT(age)
2
FROM users;
This time, the result string will be:
1
30,41,25,30,37,22,27
MySQL converts the numbers to their string representations and concatenates them with the default separator (", "). Also, note that NULL values were ignored.
Custom Separator
Here is how you can use MySQL GROUP_CONCAT with separator strings that are not commas:
1
SELECT GROUP_CONCAT(name SEPARATOR "; ")
2
FROM users;
The result will be:
1
John; Jane; Michael; Emily; Chris; Sarah; David; Laura; Daniel; Emma
Note that you can use any separator including complex strings, and that there’s no need for spacing either:
1
SELECT GROUP_CONCAT(name SEPARATOR "-@@@-")
2
FROM users;
This time, you will get:
1
John-@@@-Jane-@@@-Michael-@@@-Emily-@@@-Chris-@@@-Sarah-@@@-David-@@@-Laura-@@@-Daniel-@@@-Emma
Distinct Values
Assume you want to get only the unique age values of your users. You can achieve that with the DISTINCT as follows:
1
SELECT GROUP_CONCAT(DISTINCT age)
2
FROM users;
The result will be:
1
22,25,27,30,37,41
Note that there is only a single 30 in that string, which means the duplicate value was not considered in the final resulting set.
Note: Remember that MySQL applies an implicit sort behind the scenes as part of its DISTINCT optimizations. Here is why the numbers in the result string appear in ascending order.
Multiple Columns
GROUP_CONCAT in MySQL can also be used with multiple columns as below:
1
SELECT GROUP_CONCAT(name, " ", surname)
2
FROM users;
In this case, the result string will be:
1
John Doe,Jane Smith,Michael Johnson,Emily Davis,Chris Brown,Sarah Wilson,David Lee,Laura Martinez,Daniel Garcia,Emma Miller
As you can see, GROUP_CONCAT aggregates names and surnames before merging them.
Result Sorting
You can sort the results in a descending order after aggregating them, as in the following example:
1
SELECT GROUP_CONCAT(name ORDER BY name DESC)
2
FROM users;
The result will be:
1
Sarah,Michael,Laura,John,Jane,Emma,Emily,David,Daniel,Chris
Notice how the strings are in a descending—an inverse alphabetical—order.
Similarly, you can order them in ascending order using ASC.
Use Cases of GROUP_CONCAT in MYSQL
Time to explore the most common MySQL GROUP_CONCAT use cases!
Note: The queries in the following examples will be executed in DbVisualizer, the database client with the highest user satisfaction on the market. However, any other database client will do.
Text Aggregation
GROUP_CONCAT is useful when it comes to data aggregation, for example for generative CSV-like data:
1
SELECT GROUP_CONCAT(id, ",", name, ",", surname, ",", email, ",", age SEPARATOR "\\n") AS csv_output
2
FROM users;
The result string will have CSV format:
GROUP_BY Queries
Assume you want to get the list of full names sorted alphabetically of the employees for each department. You can reach that goal with a MySQL GROUP_CONCAT query as below:
1
SELECT D.name,
2
GROUP_CONCAT(E.full_name ORDER BY E.full_name ASC SEPARATOR ", ") AS names
3
FROM departments D
4
LEFT JOIN employees E ON E.department_id = D.id
5
GROUP BY D.name
6
ORDER BY D.name ASC;
The result will be:
GROUP_CONCAT plays nicely with GROUP BY clauses for simplified data aggregation, doesn’t it?
Subqueries
The GROUP_CONCAT MySQL function can also be used in SQL subqueries to associate aggregated strings with records in the main table.
For example, you can use GROUP_CONCAT to get a list of employees’ full names for each department and sort the entire list in an alphabetical fashion:
1
SELECT D.name,
2
(SELECT GROUP_CONCAT(E.full_name ORDER BY E.full_name ASC SEPARATOR ", ")
3
FROM employees E
4
WHERE E.department_id = D.id) AS names
5
FROM departments D;
The result will be something like:
Note how GROUP_CONCAT comes in handy for data aggregation in subqueries. In particular, the above result is just like that of the first use case.
Et voilà! You are now the master of GROUP_CONCAT in MySQL.
Conclusion
This guide has walked you through GROUP_CONCAT in MySQL. You saw its syntax and which use cases it covers. Now, you know that GROUP_CONCAT helps you aggregate strings from multiple rows into a single result string.
Completing complex operations like data aggregation becomes easier with a tool for visual database management. This is exactly what a top-notch database client such as DbVisualizer offers!
In addition to full support for MySQL and other databases, this tool comes with in-line data editing, advanced query optimization, and drag-and-drop query construction capabilities. Download DbVisualizer for free now!
FAQ
Is the MySQL GROUP_CONCAT function part of the ISO/ANSI SQL standard?
No, the GROUP_CONCAT function is not part of the ISO/ANSI SQL standard. In detail, it is a MySQL-specific extension for concatenating values from multiple rows into a single string. While similar functionality may exist in other databases (e.g. STRING_AGG in PostgreSQL), GROUP_CONCAT itself is unique to MySQL and MariaDB.
Is it possible to use the GROUP_CONCAT MySQL function in a query without the GROUP_BY clause?
Yes, GROUP_CONCAT can also be used in queries without the GROUP BY clause. In this case, the function aggregates all rows from the result set into a single concatenated string.
What is the difference between CONCAT and GROUP_CONCAT in MySQL?
How to use MySQL GROUP_CONCAT with separator strings?
To utilize MySQL GROUP_CONCAT with separator strings in MySQL, include the SEPARATOR keyword followed by the desired string. For example:
1
SELECT GROUP_CONCAT(column_name **SEPARATOR ", "**) AS result
2
FROM table_name;
This will concatenate the values of column_name with a comma and space as the separator.
What is the difference between GROUP_CONCAT and CONCAT_WS in MySQL?
