MySQL group_concat Function Explained

We already saw in our previous post that how MySQL Concat function works. Now Concat function concatenates different column values of the same row. In order to concatenate the values of a particular column of different rows, you may use the MySQL group_concat Function. In this article, I will discuss the MySQL group_concat function along with some examples.

What Is MySQL group_concat Function?

“The MySQL group_concat function concatenates the not-null values from the group and gives output as a string of concatenated values.
It is extremely helpful during aggregation of many-to-many information”

Syntax

GROUP_CONCAT(DISTINCT expression
    ORDER BY expression
    SEPARATOR sep);

Table Structure

Let’s discuss some examples of how to use the MySQL group_concat Function. In order to follow the tutorial with me, you may create the following table structure:

  1. people: It stores the name of various people and their id’s.
  2. book: stores the name of the books and their ids.
  3. user_book: stores the name of the books that each person reads (from the people table) and order of their preference.

Create and Insert statements for above tables are given below:

CREATE TABLE people (
    id INT(11) DEFAULT NULL,
    name VARCHAR(255) DEFAULT NULL
);
 CREATE TABLE books (
    id INT(11) DEFAULT NULL,
    name VARCHAR(255) DEFAULT NULL
);
CREATE TABLE people_books (
    id_people INT(11) DEFAULT NULL,
    id_book INT(11) DEFAULT NULL,
    pref INT(11) DEFAULT NULL
);
INSERT INTO people (id,name) VALUES (1,'Joe'),(2,'Chandler'),(3,'Monica'),(4,'Ross'),(5,'Phoebe'),(6,'Rachel');

INSERT INTO books (id,name) VALUES (1,'Harry Potter'), (2,'The Great Gatsby'), (3,'Kite Runner') , (4,'Thousand Splendid Suns');

INSERT INTO people_books (id_user,id_book,pref) VALUES ('1','2','1'),('2','2','1'), ('2','3','3') ,
 ('3','1','2'), ('3','3','1'), ('4','1','1'), ('4','4','2') , ('5','2','1'), ('5','3','2'),
 ('6','3','1');

A Basic Query

Now let us first begin with a very basic query. If we want to concatenate the name of every person from people table, we would write something like this:

mysql> SELECT group_concat(name) FROM people;
+----------------------------------------+
 | group_concat(name) |
 +----------------------------------------+
 | Joe,Chandler,Monica,Ross,Phoebe,Rachel |
 +----------------------------------------+

So here we just supplied name as a parameter to our group_concat function and left the rest for MySQL to do.
Please be very cautious with the length of concatenated string which will be returned by group_concat. The length of the string returned by default is 1024 characters which is pretty short and after that, the string gets truncated.
Although you can increase the limit with group_concat_max_len MySQL configuration variable, but it too has a max value of 18446744073709551615 characters (Just hope this one doesn’t fall short for your string! :D)

Using User-Defined Separators

By default, group_concat uses a comma(,) as a separator between the values which is the literal value inserted between values in a group.
If you wish then you can change it for your query result as described below:

mysql> select group_concat(name separator '\n') from people;
 +----------------------------------------+
 | group_concat(name separator '\n') |
 +----------------------------------------+
 | Joe
 Chandler
 Monica
 Ross
 Phoebe
 Rachel |
 +----------------------------------------+

Here newline character has been used for the ease of readability, you can use whichever one you want.

Choosing Distinct Elements

If you want to choose distinct elements in group_concat by eliminating the duplicate values from the output string, you may use the keyword DISTINCT before the column in the group_concat statement. This is demonstrated as follows:

mysql> select group_concat(DISTINCT name) from people;
 +----------------------------------------+
 | group_concat(DISTINCT name) |
 +----------------------------------------+
 | Joe,Chandler,Monica,Ross,Phoebe,Rachel |
 +----------------------------------------+

ORDER BY With group_concat

Another clause that is useful with group_concat is the ORDER BY clause. It gives you the string of values ordered in the specified order. The ordering is done by default in ascending order.

SELECT group_concat(name order by name asc separator '\n' ) FROM people;
 +------------------------------------------------------+
 | group_concat(name order by name asc separator '\n' ) |
 +------------------------------------------------------+
 | Chandler
 Joe
 Monica
 Phoebe
 Rachel
 Ross |
 +------------------------------------------------------+

Always use order by clause before your separator or else MySQL will give you a syntax error. MySQL engine first selects the values, and then orders them before finally separating them with the provided separator.

Aggregate Query

So let us sum up every point that we have read above and apply it in an aggregate query. Our goal is to fetch the data which has rows grouped according to people and another column which contains books reads by people but in order of the preference (1 being the most preferred and 10 the least preferred).
In this case, we will make use of group_concat function as described below:

mysql> SELECT p.name, group_concat( pb.id_book ORDER BY pref ) as order_of_prefrence_of_book_is , group_concat( b.name ORDER BY pb.pref ) as order_of_prefrence_of_books FROM people p INNER JOIN people_books pb ON pb.id_user = p.id INNER JOIN books b ON b.id = pb.id_book GROUP BY pb.id_user;
 +----------+-------------------------------+-------------------------------------+
 | name | order_of_prefrence_of_book_is | order_of_prefrence_of_books |
 +----------+-------------------------------+-------------------------------------+
 | Joe | 2 | The Great Gatsby |
 | Chandler | 2,3 | The Great Gatsby,Kite Runner |
 | Monica | 3,1 | Kite Runner,Harry Potter |
 | Ross | 1,4 | Harry Potter,Thousand Splendid Suns |
 | Phoebe | 2,3 | The Great Gatsby,Kite Runner |
 | Rachel | 3 | Kite Runner |
 +----------+-------------------------------+-------------------------------------+

NOTE: Do not forget to use group_by clause in the end otherwise you will get unexpected and erroneous result!

In the nutshell above query is just joining three tables mentioned earlier and making use of group_concat function to concat the names of books read by user and ordering them in the preference, alias name for the columns have been used.

Also, you can now use LIMIT clause with your group_concat function if you are using maria db version 10.3.3 and above.

Conclusion

So in this article, you learned about MySQL group_concat Function and using other options along with it like order by, distinct and using a user-defined separator.

You may also go through other posts related to MySQL on Concatly. Please leave your valuable comments to help me improve this resource.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *