MySQL GROUP_CONCAT

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

COUNT tells you how many rows are in a group. GROUP_CONCAT tells you what they were, by joining their values into 1 string. It is the aggregate that keeps the detail instead of reducing it to a number. Open the client with mysql -u root -p sakila before you start.

How GROUP_CONCAT works

SELECT GROUPING_COLUMN,
       GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR 'SEPARATOR_TEXT')
FROM TABLE_NAME
GROUP BY GROUPING_COLUMN;

Replace GROUPING_COLUMN with the column that defines the groups, TABLE_NAME with the table, COLUMN_NAME with the column to join, and SEPARATOR_TEXT with what goes between the values. Both ORDER BY and SEPARATOR are optional, and they live inside the brackets rather than in the query around them. Without SEPARATOR the values are joined with a comma and no space. Without ORDER BY they arrive in no particular order.

Examples

1) Join the values in each group

SELECT rating, GROUP_CONCAT(title ORDER BY title SEPARATOR ', ') AS titles
FROM film
WHERE length = 185
GROUP BY rating
ORDER BY rating;

+--------+--------------------------------------------------+
| rating | titles                                           |
+--------+--------------------------------------------------+
| G      | CONTROL ANTHEM, DARN FORRESTER, MUSCLE BRIGHT    |
| PG     | WORST BANGER                                     |
| PG-13  | CHICAGO NORTH, GANGS PRIDE, POND SEATTLE         |
| R      | HOME PITY, SOLDIERS EVOLUTION, SWEET BROTHERHOOD |
+--------+--------------------------------------------------+
4 rows in set (0.00 sec)

4 rows rather than 5, because no NC-17 film runs 185 minutes and WHERE removed its rows before the grouping. A group with no rows left does not appear.

2) Drop the repeats

DISTINCT inside the brackets removes duplicate values within each group.

SELECT rating, GROUP_CONCAT(DISTINCT rental_rate ORDER BY rental_rate) AS rates
FROM film
GROUP BY rating
ORDER BY rating;

+--------+----------------+
| rating | rates          |
+--------+----------------+
| G      | 0.99,2.99,4.99 |
| PG     | 0.99,2.99,4.99 |
| PG-13  | 0.99,2.99,4.99 |
| R      | 0.99,2.99,4.99 |
| NC-17  | 0.99,2.99,4.99 |
+--------+----------------+
5 rows in set (0.00 sec)

Without DISTINCT each string would hold 1 copy of the rate per film in the group, between 178 and 223 of them, and the 2 longest would run into the limit the next section is about. This is also the default separator: a comma and nothing else.

The result is cut off at group_concat_max_len bytes

This is the trap. The server truncates a GROUP_CONCAT result that gets too long, and it does not raise an error. The limit is a setting, so the number below is this server’s rather than a property of MySQL, and it counts bytes rather than characters.

SELECT LENGTH(GROUP_CONCAT(title)) AS bytes FROM film;

+-------+
| bytes |
+-------+
|  1024 |
+-------+
1 row in set, 1 warning (0.00 sec)

Exactly 1,024, which is suspicious on its own: a real total would rarely land on a round number. Check it against the setting.

SELECT @@group_concat_max_len AS max_len;

+---------+
| max_len |
+---------+
|    1024 |
+---------+
1 row in set (0.00 sec)

The footer of the first query reported a warning, and SHOW WARNINGS says what was lost: Warning 1260 Row 65 was cut by GROUP_CONCAT(). The string holds 64 whole titles out of 1,000 and then stops in the middle of the 65th. Run SHOW WARNINGS yourself after a GROUP_CONCAT to see the same message. SET SESSION group_concat_max_len = 100000; raises the limit for the rest of your connection and reverts when you reconnect. Because the limit counts bytes, text outside ASCII is cut sooner than the number suggests: a character can take up to 4 bytes in utf8mb4. Treat a result whose length equals group_concat_max_len exactly as truncated until you have checked, and watch the warning count in the footer.

Summary

Use GROUP_CONCAT to join the values in a group into 1 string.
Put ORDER BY, SEPARATOR and DISTINCT inside the brackets.
Expect a comma with no space when you name no separator.
Expect truncation at group_concat_max_len bytes, with a warning rather than an error.
Suspect truncation whenever the length equals the limit exactly.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How GROUP_CONCAT works

Examples

1) Join the values in each group

2) Drop the repeats

The result is cut off at group_concat_max_len bytes

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How GROUP_CONCAT works

​Examples

​1) Join the values in each group

​2) Drop the repeats

​The result is cut off at group_concat_max_len bytes

​Summary

​See also

How GROUP_CONCAT works

Examples

1) Join the values in each group

2) Drop the repeats

The result is cut off at group_concat_max_len bytes

Summary

See also