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 −

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 −

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 −