Read more

MySQL: For each group, retrieve a comma-separated list of values in a given column

Henning Koch
September 29, 2011Software engineer at makandra GmbH

The technique described in this card has an important caveat: The result of GROUP_CONCAT is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. This will cause horrible, data-destroying bugs in production. For this reason you should probably not use GROUP_CONCAT ever. At least you must set the value of group_concat_max_len to an insanely high value on every database server your application runs on.


Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

Like MAX or COUNT, GROUP_CONCAT Show archive.org snapshot is a MySQL aggregation function you can use whenever your query contains a GROUP BY. You can use it to retrieve a comma-separated list of all values in a given column within each group.

For example, you have a table posts:

| id | user_id | title |
+----+---------+-----------------+
| 8 | 5 | How to rock |
+----+---------------------------+
| 9 | 5 | Shoes are weird |
+----+---------------------------+
| 10 | 5 | Best song |
+----+---------------------------+
| 11 | 6 | Sunny day |
+----+---------------------------+
| 12 | 6 | Greetings |
+----+---------------------------+

For each user_id we would like a comma-separated list of post IDs. GROUP_CONCAT lets you say this:

SELECT user_id, GROUP_CONCAT(id) FROM posts GROUP BY user_id;

The result set looks like this:

| user_id | GROUP_CONCAT(id) |
+---------+------------------+
| 5 | 8,9,10 |
+----------------------------+
| 6 | 11,12 |
+----+-----------------------+

Using GROUP_CONCAT with joined tables

GROUP_CONCAT works with joins as well.

Let's say we have a table courses:

| id | name |
+----+---------------+
| 1 | Ruby 101 |
+----+---------------+
| 2 | TDD for Poets |
+----+---------------+

We also have a second table bookings:

| id | course_id |
+----+-----------+
| 7 | 1 |
+----+-----------+
| 8 | 1 |
+----+-----------+
| 9 | 1 |
+----+-----------+
| 10 | 2 |
+----+-----------+
| 11 | 2 |
+----+-----------+

For each course we would like a comma-separated list of booking IDs. GROUP_CONCAT lets you say this:

SELECT courses.name, GROUP_CONCAT(bookings.id) FROM bookings INNER JOIN courses ON courses.id == bookings.course_id GROUP BY bookings.course_id;

The result set looks like this:

| courses.name | GROUP_CONCAT(bookings.id) |
+---------------+---------------------------+
| Ruby 101 | 7,8,9 |
+---------------+---------------------------+
| TDD for Poets | 10,11 |
+---------------+---------------------------+

Henning Koch
September 29, 2011Software engineer at makandra GmbH
Posted by Henning Koch to makandra dev (2011-09-29 15:07)