search
Search
Publish
menu
menu search toc more_vert
Robocat
Guest 0reps
Thanks for the thanks!
close
Comments
Log in or sign up
Cancel
Post
account_circle
Profile
exit_to_app
Sign out
help Ask a question
Share on Twitter
search
keyboard_voice
close
Searching Tips
Search for a recipe: "Creating a table in MySQL"
Search for an API documentation: "@append"
Search for code: "!dataframe"
Apply a tag filter: "#python"
Useful Shortcuts
/ to open search panel
Esc to close search panel
to navigate between search results
d to clear all current filters
Enter to expand content preview
icon_star
Doc Search
icon_star
Code Search Beta
SORRY NOTHING FOUND!
mic
Start speaking...
Voice search is only supported in Safari and Chrome.
Navigate to
A
A
share
thumb_up_alt
bookmark
arrow_backShare
Twitter
Facebook

MySQL | GROUP_CONCAT method

Database
chevron_right
MySQL
chevron_right
Documentation
chevron_right
Functions
chevron_right
Aggregate
schedule Mar 10, 2022
Last updated
local_offer MySQL
Tags
tocTable of Contents
expand_more

MySQL's GROUP_CONCAT(~) method concatenates non-NULL values from the same group together.

NOTE

The maximum allowed length of the concatenated value is controlled by system variable group_concat_max_len, which has a default value of 1024.

Syntax

SELECT column1,
GROUP_CONCAT(DISTINCT column2
ORDER BY column2 ASC|DESC
SEPARATOR ' ')
FROM table
GROUP BY column2

Examples

Consider the following table about whether students hold a drivers license:

student_id

full_name

age

has_license

1

Sky Towner

17

0

2

Ben Davis

19

1

3

Travis Apple

18

0

4

Arthur David

16

0

5

Benjamin Town

17

1

The above sample table can be created using the code here.

Basic usage

To concatenate the full names of students who are the same age:

SELECT age, GROUP_CONCAT(full_name)
FROM drivers_license
GROUP BY age;
+------+--------------------------+
| age | GROUP_CONCAT(full_name) |
+------+--------------------------+
| 16 | Arthur David |
| 17 | Sky Towner,Benjamin Town |
| 18 | Travis Apple |
| 19 | Ben Davis |
+------+--------------------------+

As Sky Towner and Benjamin Town are both 17, we return the two names concatenated.

DISTINCT

To group by has_license and concatenate distinct ages in each group:

SELECT has_license, GROUP_CONCAT(DISTINCT age)
FROM drivers_license
GROUP BY has_license;
+-------------+----------------------------+
| has_license | GROUP_CONCAT(DISTINCT age) |
+-------------+----------------------------+
| 0 | 16,17,18 |
| 1 | 17,19 |
+-------------+----------------------------+

ORDER BY

To specify the order in which values are concatenated:

SELECT age, GROUP_CONCAT(full_name ORDER BY student_id DESC)
FROM drivers_license
GROUP BY age;
+------+--------------------------------------------------+
| age | GROUP_CONCAT(full_name ORDER BY student_id DESC) |
+------+--------------------------------------------------+
| 16 | Arthur David |
| 17 | Benjamin Town,Sky Towner |
| 18 | Travis Apple |
| 19 | Ben Davis |
+------+--------------------------------------------------+

In age group 17, the student with larger student id, 'Benjamin Town' is concatenated first followed by 'Sky Towner'.

SEPARATOR

To use ' / ' as the separator for concatenation:

SELECT age, GROUP_CONCAT(full_name SEPARATOR ' / ')
FROM drivers_license
GROUP BY age;
+------+-----------------------------------------+
| age | GROUP_CONCAT(full_name SEPARATOR ' / ') |
+------+-----------------------------------------+
| 16 | Arthur David |
| 17 | Sky Towner / Benjamin Town |
| 18 | Travis Apple |
| 19 | Ben Davis |
+------+-----------------------------------------+

Note that concatenated records are now separated by ' / '.

robocat
Published by Arthur Yanagisawa
Edited by 0 others
Did you find this page useful?
thumb_up
thumb_down
Ask a question or leave a feedback...