EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL GROUP_CONCAT()

MySQL GROUP_CONCAT()

By Aanchal SharmaAanchal Sharma

MySQL GROUP_CONCAT()

Introduction to MySQL GROUP_CONCAT()

MySQL GROUP_CONCAT() function is an aggregate GROUP BY function which is responsive for concatenating data column values from several rows into one field with string values with the help of some MySQL clauses. If the rows group comprises at least a single or no-NULL column value, then the GROUP_CONCAT function returns a sequence value. Otherwise, the result will be NULL. Here are some of the constraints and clauses that can be used with the GROUP_CONCAT() function in MySQL:

Distinct: It is applied to return the unique string values in the group before the function concatenates the values. It removes the repeated values from the result rows.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Order By: By default, the sorted order of the result from the function is ascending, but we can use this clause to categorize the group data in ascending or descending order.

Separator: To separate the string value, we can use the default operator as comma(,) or look for a different literal value.

Syntax

Here is the syntax for the GROUP_CONCAT() function:

SELECT Column1,Column2, …,ColumnNGROUP_CONCAT([DISTINCT] ColumnName1 [ORDER BY {ASC or, DESC}] [SEPARATOR {comma or any literal value}]) FROM TableName GROUP BY ColumnName2;

Let us discuss the terms used in the above context:

  • Column1,…CoulmnN: Denotes the name of the columns where we apply the function in the table.
  • ColumnName1: Defines the column values, which will be concatenated into one row for every group.
  • ColumnName2: The GROUP BY clause in MySQL defines the column to be used for grouping the results to be fetched.
  • TableName: The database table name where we use the GROUP_CONCAT() function.

How does MySQL GROUP_CONCAT() function work?

  • The MySQL GROUP_CONCAT() function returns a result from a table that contains a row with a group of string values separated by a comma or another given value. This function helps fetch records according to a grouping process and collect data for evaluation.
  • The MySQL CONCAT() function generates a result limited to a maximum length of 1024 in MySQL, as defined by the system variable group_concat_max_len. However, this limit can be modified at runtime using the SET command as follows:
SET [GLOBAL | SESSION] group_concat_max_len = LengthValue ;

LengthValue denotes the variable value to exceed the length of the result produced by the CONCATE() function.

We can use various applications of MySQL CONCAT() function; some are mentioned below:

  • We can create comma-separated user roles such as Admin, Editor, Author or Subscriber, etc.
  • It can display the list of hobbies of different users separated by commas like Designer, Painter, etc.
  • People commonly use MySQL’s GROUP_CONCAT() function to generate tags for articles, blog posts, guest posts, products, etc. For example, tags like “MySQL coding,” “Learn MySQL,” and “clauses” can be generated using this function.

Examples of MySQL GROUP_CONCAT()

Let us try some examples to understand the MySQL CONCATE() function. For this, let us first create a table as a sample one to work with and insert some records into it.

Creating a table:

CREATE TABLE Books (BookID int NOT NULL, BookName varchar(255) NOT NULL, Language varchar(255), Price int, PRIMARY KEY (BookID) );

Entering records into the table using the following statement:

INSERT INTO Books (BookID, BookName, Language, Price) VALUES
('101', 'Algebraic Maths', 'English', '2000'),
('102', 'Networking', 'English', '5000'),
('103', 'Nanoscience', 'Hindi', '3000'),
('104', 'Web Design', 'English', '2000'),
('105', 'Nuclear Science', 'English', '2100'),
('106', 'Nuclear Science', 'Hindi', '1980'),
('107', 'Electrical Engineering', 'Hindi', '1500'),
('108', 'Manufacturing', 'Hindi', '1800');

Displaying the table:

SELECT * FROM Books;

Output:

MySQL GROUP_CONCAT() output 1

Now let us start with these examples:

Example #1 – Simple MySQL_CONCAT() function

Let us create the statement which returns a list of books separated by a comma in each language group.

SELECT Language, GROUP_CONCAT(BookName) FROM Books GROUP BY Language;

Output:

MySQL GROUP_CONCAT() output 2

Example #2 – MySQL CONCAT() function with DISTINCT clause

Here, the MySQL query returns unique string values as grouped by Language, removing the duplicate ones if present for each group in the column values using GROUP_CONCAT() function.

SELECT Language, GROUP_CONCAT(DISTINCT BookName) FROM Books GROUP BY Language;

Output:

MySQL GROUP_CONCAT() output 3

Example #3 – MySQL CONCAT() function with ORDER BY Clause

In this example, we will use the MySQL statement to retrieve a list of distinct BookName values separated by commas. As you see in the result, the fetched rows are sorted in ascending order using the ORDER BY clause; you can also use DESC sorting.

SELECT Language, GROUP_CONCAT(DISTINCT BookName) FROM Books GROUP BY Language ORDER BY GROUP_CONCAT(DISTINCT BookName) ASC;

Output:

MySQL GROUP_CONCAT() output 4

Example #4 – MySQL CONCAT() function with Separator

In this example, the MySQL CONCAT() function will return the list series of BookName grouped by Language with distinct string values but separated by adding a specific separator (“) space. We can also use other literal values like semicolon(;), colon(:), etc. The SELECT MySQL query below includes an option to display the result rows in descending order.

SELECT Language, GROUP_CONCAT(DISTINCT BookName ORDER BY BookName DESC SEPARATOR ' ') FROM Books GROUP BY Language;

Output:

output 5

Example #5 – MySQL CONCAT() function: some mistakes

Since the result returned by the GROUP_CONCAT function is a single string, not a list of values, it concatenates all the values in one list denoted as a single string. Experts do not recommend using the GROUP_CONCAT() function for the operator IN within an inner query in MySQL.

For example, the result of values: 21,2 and 23 when applied GROUP_CONCAT() function will be as a string:’ 21,22,23′.

IN operator receives a series of values but not a string, including the list of column values. Therefore if you provide this type of result using IN operator in MySQL, you do not get the expected result, and the query below will not work as shown in the output.

SELECT BookID, BookName FROM Books WHEREBookID IN GROUP_CONCAT(BookID);

Output:

output 6

We need to use the ORDER BY clause in the GROUP_CONCAT properly () function query statement to get the desired output. This is an aggregate function, so we should use ORDER BY inside the function but not in the SELECT statement part.

The succeeding query represents the incorrect usage of such a mistake:

SELECT GROUP_CONCATE(DISTINCT BookName SEPARATOR ' ; ') FROM Books ORDER BY BookName;

Output:

output 7

Conclusion

  • The MySQL CONCAT() function on execution results in binary or non-binary string values, which depend on the particular arguments or parameters provided in the function during code definition.
  • This function can be beneficial to return the column values displaying in a single field using ORDER BY and GROUP BY clauses, respectively, to get the desired result from column table values.

Recommended Articles

We hope that this EDUCBA information on “MySQL GROUP_CONCAT()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Unique Key in MySQL
  2. MySQL Self Join
  3. NOT in MySQL
  4. Cassandra vs MySQL
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more