MySQL - GROUP_CONCAT() Function

---

---

The MySQL GROUP_CONCAT() function concatenates all the NON NULL values in a column of a database table and returns the combined result. All the values in this column, irrespective of their datatype, are concatenated using commas and returned as a string value. If the specified column have no NON NULL values, this function returns NULL.

Syntax

Following is the syntax of MySQL GROUP_CONCAT() function −

CONCAT(expr);

Parameters

This method accepts a parameter. The same is described below −

• expr: The column or expression whose values we want to concatenate.

Return value

This function returns a string that consists of concatenated values from the specified column or expression.

Example

Following is an example demonstrating the usage of this function. Assume we have created a table with name CUSTOMERS using the CREATE TABLE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

To display all the records that are inserted in the CUSTOMERS table, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Following query retrieves a concatenated string of all the 'NAME' values from the 'CUSTOMERS' table −

SELECT GROUP_CONCAT(NAME) From CUSTOMERS;

This will produce the following result −

GROUP_CONCAT(NAME)
Ramesh,Khilan,Kaushik,Chaitali,Hardik,Komal,Muffy

Here, we are selecting the 'AGE' and aggregates the 'SALARY' values into a JSON array named 'Salaries', grouping the results by the 'AGE' column in the 'CUSTOMERS' table. −

SELECT AGE, GROUP_CONCAT(SALARY) As Salaries From CUSTOMERS 
GROUP BY AGE;

This will produce the following result −

AGE	Salaries
22	4500.00
23	2000.00
24	10000.00
25	1500.00,6500.00
27	8500.00
32	2000.00