MySQL Function : GROUP_CONCAT()

Column 1

Return a concatenated string.

Status

Not started

MySQL Function : GROUP_CONCAT()

Concatenates values into a string

The GROUP_CONCAT() function in MySQL is an aggregate function that joins multiple row values into a single string. It's especially useful for generating summarized lists, CSV-style strings, and grouped information.

Syntax


GROUP_CONCAT(
  [DISTINCT] expression
  [ORDER BY expression [ASC|DESC]]
  [SEPARATOR 'separator']
)

Key Options:

DISTINCT: Removes duplicates before concatenating.
ORDER BY: Sorts the values before joining.
SEPARATOR: Defines the delimiter between values (default is comma).

NOTE

The DISTINCT clause allows you to eliminate duplicate values in the group before concatenating them.
The ORDER BY clause allows you to sort the values in ascending or descending order before concatenating. By default, it sorts the values in ascending order. If you want to sort the values in the descending order, you need to specify explicitly the DESC option.
The SEPARATOR specifies a literal value inserted between values in the group. If you do not specify a separator, the GROUP_CONCAT function uses a comma (,) as the default separator.
The GROUP_CONCAT function ignores NULL values. It returns NULL if there are no matching rows or all arguments are NULL values.
The GROUP_CONCAT function returns a binary or non-binary string, which depends on the arguments.
By default, the maximum length of the return string is 1024. If you need more than this, you can extend the maximum length by setting the group_concat_max_len system variable at SESSION or GLOBAL level.

Reference Table: `customers`

customer_id	customer_name	country	contact_firstname	contact_lastname	sales_rep_id
1	Acme Corp	USA	John	Doe	101
2	Beta Ltd	UK	Anna	Smith	101
3	Gamma Inc	USA	Mike	Taylor	101
4	Delta LLC	Germany	Eva	Brown	102
5	Omega GmbH	Germany	Tom	White	102
6	Sigma AG	UK	Liam	Davis	102
7	Theta Ltd	UK	John	Smith	102
8	Alpha Inc	USA	John	Adams	NULL

‣

Example 1: Basic Use

‣

Example 2: Using DISTINCT

‣

Example 3: Using ORDER BY

‣

Example 4: Using a Custom Separator

‣

Example 5: With JOIN and GROUP BY

‣

Example 6: Combining with CONCAT_WS()

‣

Invalid Usage Examples

‣

Controlling Output Length

Summary Table

Feature	Example Code Snippet	Notes
Basic use	`GROUP_CONCAT(column)`	Joins all values
Remove duplicates	`GROUP_CONCAT(DISTINCT column)`	Skips duplicate values
Order the output	`GROUP_CONCAT(column ORDER BY column DESC)`	Sorts before joining
Custom separator	`GROUP_CONCAT(column SEPARATOR '	')`
Merge multiple columns	`CONCAT_WS(' ', col1, col2)` inside `GROUP_CONCAT()`	Merges column values before aggregation
Use with JOIN + GROUP BY	`GROUP_CONCAT(...)` with `GROUP BY`	Produces grouped aggregates
Increase result length	`SET SESSION group_concat_max_len = 1000000`	Avoids truncation for large data
Invalid usage	`IN (GROUP_CONCAT(...))`	Doesn't work – returns a single string
Wrong ORDER BY	`ORDER BY` outside `GROUP_CONCAT()`	Ineffective – must be inside the function

NOTE 1

MySQL GROUP_CONCAT() function examples

Let’s take a look at the customers table in the sample database:

To get all countries where customers are located as a comma-separated string, you use the GROUP_CONCAT() function as follows:

SELECT
    GROUP_CONCAT(country)
FROM
    customers;

France,USA,Australia,France,Norway,USA,Poland,Germany,USA,USA,Spain,Sweden,Denmark,France,Singapore,USA,USA,USA,Singapore,Norway  ,USA,Portugal,France,France,USA,USA,Japan,USA,Finland,UK,Ireland,USA,UK,Canada,USA,USA,Singapore,France,Hong Kong,Spain,USA,Germany,Denmark,Canada,Spain,USA,UK,France,Germany,Italy,France,France,Germany,Canada,Germany,Australia,Italy,Australia,USA,Switzerland,Switzerland,Norway  ,Netherlands,Germany,Finland,Belgium,USA,USA,USA,New Zealand,UK,USA,Australia,Finland,Germany,USA,Spain,USA,Ireland,France,France,South Africa,New Zealand,Germany,USA,USA,Portugal,Switzerland,USA,Belgium,Austria,Philippines,Italy,Japan,France,Germany,New Zealand,Germany,USA,Germany,USA,Sweden,USA,Austria,USA,USA,Spain,Germany,USA,Spain,Australia,Italy,USA,Germany,Russia,Israel,Spain,USA,USA,UK,USA,New Zealand

However, some customers are located in the same country. To remove the duplicate country’s names, you add the DISTINCT clause as the following query:

SELECT 
    GROUP_CONCAT(DISTINCT country)
FROM
    customers;

Australia,Austria,Belgium,Canada,Denmark,Finland,France,Germany,Hong Kong,Ireland,Israel,Italy,Japan,Netherlands,New Zealand,Norway,Norway  ,Philippines,Poland,Portugal,Russia,Singapore,South Africa,Spain,Sweden,Switzerland,UK,USA

It is more readable if the country’s names are in ascending order. To sort the country’s name before concatenating, you use the ORDER BY clause as follows:

SELECT 
    GROUP_CONCAT(DISTINCT country ORDER BY country)
FROM
    customers;

Australia,Austria,Belgium,Canada,Denmark,Finland,France,Germany,Hong Kong,Ireland,Israel,Italy,Japan,Netherlands,New Zealand,Norway,Norway  ,Philippines,Poland,Portugal,Russia,Singapore,South Africa,Spain,Sweden,Switzerland,UK,USA

To change the default separator of the returned string from a comma (,) to a semi-colon (—), you use the SEPARATOR clause as the following query:

select 
group_concat(distinct country order by country separator '__') from customers

Australia__Austria__Belgium__Canada__Denmark__Finland__France__Germany__Hong Kong__Ireland__Israel__Italy__Japan__Netherlands__New Zealand__Norway__Norway  __Philippines__Poland__Portugal__Russia__Singapore__South Africa__Spain__Sweden__Switzerland__UK__USA

NOTE 2

Great! now you know how the GROUP_CONCAT() function works. Let’s put it in a practical example.

Each customer has one or more sales representatives. In other words, each sales employee is in charge of one or more customers. To find out who is in charge of which customers, you use the inner join clause as follows:

SELECT
    employeeNumber,
    firstname,
    lastname,
    customername
FROM
    employees
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employees.employeeNumber
ORDER BY
	firstname,
    lastname;

employeeNumber  firstname  lastname   customername                        
--------------  ---------  ---------  ----------------------------------  
1611            Andy       Fixter     Australian Collectors, Co.          
1611            Andy       Fixter     Anna's Decorations, Ltd             
1611            Andy       Fixter     Souveniers And Things Co.           
1611            Andy       Fixter     Australian Gift Network, Co         
1611            Andy       Fixter     Australian Collectables, Ltd        
1504            Barry      Jones      Baane Mini Imports                  
1504            Barry      Jones      Blauer See Auto, Co.                
1504            Barry      Jones      Volvo Model Replicas, Co            
1504            Barry      Jones      Herkku Gifts                        
1504            Barry      Jones      Clover Collections, Co.             
1504            Barry      Jones      Toms Spezialitäten, Ltd             
1504            Barry      Jones      Norway Gifts By Mail, Co.           
1504            Barry      Jones      Bavarian Collectables Imports, Co.  
1504            Barry      Jones      Scandinavian Gift Ideas             
1286            Foon Yue   Tseng      Muscle Machine Inc                  
1286            Foon Yue   Tseng      American Souvenirs Inc              
1286            Foon Yue   Tseng      Vitachrome Inc.                     
1286            Foon Yue   Tseng      Québec Home Shopping Network        
1286            Foon Yue   Tseng      Classic Legends Inc.                
1286            Foon Yue   Tseng      Super Scale Inc.                    
1286            Foon Yue   Tseng      Microscale Inc.

Now, we can group the result set by the employee number and concatenate all employees that are in charge of the employee by using the GROUP_CONCAT() function as follows:

SELECT 
    employeeNumber,
    firstName,
    lastName,
    GROUP_CONCAT(DISTINCT customername
        ORDER BY customerName)
FROM
    employees
        INNER JOIN
    customers ON customers.salesRepEmployeeNumber = employeeNumber
GROUP BY employeeNumber
ORDER BY firstName , lastname;

employeeNumber  firstName  lastName   customer_list                                                                                                                                                                                                               
--------------  ---------  ---------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
1611            Andy       Fixter     Anna's Decorations, Ltd--Australian Collectables, Ltd--Australian Collectors, Co.--Australian Gift Network, Co--Souveniers And Things Co.                                                                                   
1504            Barry      Jones      Baane Mini Imports--Bavarian Collectables Imports, Co.--Blauer See Auto, Co.--Clover Collections, Co.--Herkku Gifts--Norway Gifts By Mail, Co.--Scandinavian Gift Ideas--Toms Spezialitäten, Ltd--Volvo Model Replicas, Co  
1286            Foon Yue   Tseng      American Souvenirs Inc--Classic Legends Inc.--Microscale Inc.--Muscle Machine Inc--Québec Home Shopping Network--Super Scale Inc.--Vitachrome Inc.                                                                          
1323            George     Vanauf     Canadian Gift Exchange Network--Gift Depot Inc.--Gift Ideas Corp.--Land of Toys Inc.--Mini Classics--Motor Mint Distributors Inc.--Royal Canadian Collectables, Ltd.--Tekni Collectables Inc.                               
1370            Gerard     Hernandez  Alpha Cognac--Atelier graphique--Auto Associés & Cie.--Daedalus Designs Imports--Euro+ Shopping Channel--La Rochelle Gifts--Mini Caravy                                                                                     
1188            Julie      Firrelli   Cambridge Collectables Co.--Classic Gift Ideas, Inc--Collectables For Less Inc.--Diecast Collectables--Mini Creations Ltd.--Online Mini Collectables                                                                        
1501            Larry      Bott       AV Stores, Co.--Double Decker Gift Stores, Ltd--giftsbymail.co.uk--Oulu Toy Supplies, Inc.--Stylish Desk Decors, Co.--Suominen Souveniers--Toys of Finland, Co.--UK Collectables, Ltd.                                      
1165            Leslie     Jennings   Corporate Gift Ideas Co.--Mini Gifts Distributors Ltd.--Mini Wheels Co.--Signal Collectibles Ltd.--Technics Stores Inc.--The Sharp Gifts Warehouse                                                                          
1166            Leslie     Thompson   Boards & Toys Co.--Collectable Mini Designs Co.--Men 'R' US Retailers, Ltd.--Signal Gift Stores--Toys4GrownUps.com--West Coast Collectables Co.                                                                             
1337            Loui       Bondur     Auto Canal+ Petit--La Corne D'abondance, Co.--Lyon Souveniers--Marseille Mini Autos--Reims Collectables--Saveley & Henriot, Co.                                                                                             
1621            Mami       Nishi      Cruz & Sons Co.--Dragon Souveniers, Ltd.--King Kong Collectables, Co.--Osaka Souveniers Co.--Tokyo Collectables, Ltd                                                                                                        
1702            Martin     Gerard     CAF Imports--Corrida Auto Replicas, Ltd--Enaco Distributors--Iberia Gift Imports, Corp.--Precious Collectables--Vida Sport, Ltd                                                                                             
1401            Pamela     Castillo   Amica Models & Co.--Danish Wholesale Imports--Frau da Collezione--Heintze Collectables--L'ordine Souveniers--Mini Auto Werke--Petit Auto--Rovelli Gifts--Royale Belge--Salzburg Collectables                                
1612            Peter      Marsh      Down Under Souveniers, Inc--Extreme Desk Decorations, Ltd--GiftsForHim.com--Handji Gifts& Co--Kelly's Gift Shop                                                                                                             
1216            Steve      Patterson  Auto-Moto Classics Inc.--Diecast Classics Inc.--FunGiftIdeas.com--Gifts4AllAges.com--Marta's Replicas Co.--Online Diecast Creations Co.

Using GROUP_CONCAT() with CONCAT_WS() function example

Sometimes, the GROUP_CONCAT function can be combined with the CONCAT_WSfunction to make the result of the query more useful.

For example, to make a list of semicolon-separated values of customers:

First, you concatenate the last name and first name of each customer’s contact using the CONCAT_WS() function. The result is the contact’s full name.
Then, you use the GROUP_CONCAT() function to make the list.

The following query makes a list of semicolon-separated values of customers.

⚠️

Note that GROUP_CONCAT() function concatenates string values in different rows while the CONCAT_WS() or CONCAT()function concatenates two or more string values in different columns.

SELECT
GROUP_CONCAT(
CONCAT_WS(', ', contactLastName, contactFirstName)
SEPARATOR ';')
FROM
customers;

Schmitt, Carine ;King, Jean;Ferguson, Peter;Labrune, Janine ;Bergulfsen, Jonas ;Nelson, Susan;Piestrzeniewicz, Zbyszek ;Keitel, Roland;Murphy, Julie;Lee, Kwai;Freyre, Diego ;Berglund, Christina ;Petersen, Jytte ;Saveley, Mary ;Natividad, Eric;Young, Jeff;Leong, Kelvin;Hashimoto, Juri;Victorino, Wendy;Oeztan, Veysel;Franco, Keith;de Castro, Isabel ;Rancé, Martine ;Bertrand, Marie;Tseng, Jerry;King, Julie;Kentary, Mory;Frick, Michael;Karttunen, Matti;Ashworth, Rachel;Cassidy, Dean;Taylor, Leslie;Devon, Elizabeth;Tamuri, Yoshi ;Barajas, Miguel;Young, Julie;Walker, Brydey;Citeaux, Frédérique ;Gao, Mike;Saavedra, Eduardo ;Young, Mary;Kloss, Horst ;Ibsen, Palle;Fresnière, Jean ;Camino, Alejandra ;Thompson, Valarie;Bennett, Helen ;Roulet, Annette ;Messner, Renate ;Accorti, Paolo ;Da Silva, Daniel;Tonini, Daniel ;Pfalzheim, Henriette ;Lincoln, Elizabeth ;Franken, Peter ;O'Hara, Anna;Rovelli, Giovanni ;Huxley, Adrian;Hernandez, Marta;Harrison, Ed;Holz, Mihael;Klaeboe, Jan;Schuyler, Bradley;Andersen, Mel;Koskitalo,

MySQL GROUP_CONCAT() function applications

There are many cases where you can apply the GROUP_CONCAT() function to produce useful results. The following list is some common examples of using the GROUP_CONCAT() function.

Make a comma-separated user’s roles such as ‘admin, author, editor’.
Produce the comma-separated user’s hobbies e.g., ‘design, programming, reading’.
Create tags for blog posts, articles, or products e.g., ‘mysql, mysql aggregate function, mysql tutorial’.

In this tutorial, you have learned how to use the MySQL GROUP_CONCAT() function to concatenate non-NULL values of a group of strings into a single string.

MySQL Function : GROUP_CONCAT()

MySQL Function : GROUP_CONCAT()

Syntax

Key Options:

Reference Table: customers

Example 1: Basic Use

Example 2: Using DISTINCT

Example 3: Using ORDER BY

Example 4: Using a Custom Separator

Example 5: With JOIN and GROUP BY

Example 6: Combining with CONCAT_WS()

Invalid Usage Examples

Controlling Output Length

Summary Table

NOTE 1

MySQL GROUP_CONCAT() function examples

NOTE 2

Using GROUP_CONCAT() with CONCAT_WS() function example

MySQL GROUP_CONCAT() function applications

Reference Table: `customers`