MySQL GROUP_CONCAT() function

Use of Various Clauses inside GROUP_CONCAT() Function

Below are some most used clauses inside the group_concat() function in MySQL.

• Distinct: This clause is used to remove duplicate or repetitive values from the result.
   
• Order By: This clause sorts the value of the group in some specific order and later concatenates them.
   
• Separator: The separator clause defines the delimiter, which separates the concatenated values. By default, a comma (",") is used as a separator, which can later be changed as per the need.

Examples

For all our examples, we will use a sales table, which stores the sales made by salesmen of a paper company. 

• The salesman field contains the name of the salesman who made the sale. 
   
• The product field contains the name of the product that was sold.
   
• The quantity field contains the number of products sold in that sale.
   
• The client field contains the name of the client to whom the sale was made.
   

The example table contains 50 rows. The first 10 rows look like this.

Now, let's go ahead and see examples using group_concat.

Example 1

Suppose we wish to see the different products sold by different salesmen. 

Command

select salesman,group_concat(product) as "Products Sold" from sales group by salesman;

 

This will select all the rows from the sales table and group them by salesman value. Then, for each group (i.e., each salesman) - it will take all the product values and concatenate them into a single string using the group_concat command. This concatenated string will be shown in a new column, "Products Sold."

Output

As we can see, the different product values have been taken for each salesman and concatenated into a single string. Since we did not specify the separator value, the MySQL group_concat function used a comma by default. 

But, you can see - many products have been repeated. This will happen because each salesman made many sales of the same product. For example - Andy made 2 sales in which he sold Glossy paper. 

To solve this, we use the distinct keyword.

Example 2 - using Distinct

If we wish to eliminate duplicate values, we can use distinct in MySQL group_concat statement. 

Command

select salesman,group_concat(distinct product) as "Products Sold" from sales group by salesman;

 

This will group rows by salesman, and for each group, select each distinct value of product, and concatenate them into a single string (separated by commas).

Output

As we can see, the duplicate values have now been eliminated. 

Suppose we also wish to see the quantities associated with each sale. We can also include the quantity column in the group_concat statement. 

Example 3 - Multiple Expressions

We can add multiple columns and hardcoded strings in MySQL group_concat function.

Command

select salesman,group_concat(product,':',quantity) as "Products Sold" from sales group by salesman;

 

This will select all the rows from the sales table, and group them by salesman value. Each group will have multiple rows, all with different values of product and quantity. The command will join the product and client value in the format product: quantity. It will concatenate all such values in the group into a single string - separated by commas.

Output

As we can see, we have listed both the product and quantity associated with each sale.

You might notice that multiple sales of the same product are listed differently. A more useful measure might be the total number of units of a particular product sold. For example - how many Glossy stocks did Jim sell?

To calculate this, we will have to use nested select statements.

Example 4 - Nested Expressions

We can use group_concat in nested statements. To calculate the sum of each product sold by each salesman, we need nested statements. 

Command

select salesman, group_concat(`Products Sold` separator '|') as "Products Sold" from 
(select salesman,concat(product,':',sum(quantity)) as "Products Sold" from sales group by salesman,product) derived_table
group by salesman;

 

We have two select statements here - an inner select, and an outer select. 

Inner Statement

The inner select groups the rows in the table by both the salesman and product fields. This means all the sales of Andy won't be in the same group.  Andy's sales of Glossy will be a separate group from Andy's sales of Card. Similarly, Jim's Sales of Legal will be a different group than Dwight's Sales of Legal. 

From these groups, we calculate the sum of quantity and concat it with the product name. NOTE - This is not group_concat. The concat function simply takes two values and concatenates them. The result of the concat function will be in the Products Sold column. 

This way, we get the name of the salesman, the name of the product, and the sum of the quantities of that product sold by that salesman. The output of the inner select statement looks like this.

Outer Statement

The outer select statement operates on the above table - which is given a temporary name derived_table. We group by salesman, and for each group (i.e., each salesman) - we perform group_concat on the Products Sold column. We also specify a separator "|" this time. 

The final output looks like this. 

We can see that each salesman's sales are listed by product, along with the total quantity of that product sold by each salesman. They are separated by the separator we specified. Now, we can easily see that Jim made 72 sales of Legal stock, and Stanley made 318 sales of Card stock. 

Example 5 - Nested Group_Concat

We can have nested statements containing group_concat. Let's say that we want to view which salesman sold which type of product to which client. For example, we want to know which clients Jim sold Legal paper to, and which clients he sold Plain paper to. 

We can use nested group_concat statements for this.

Command

select salesman, group_concat(`Products Sold` separator ' | ') as "Products Sold" from 
(select salesman,concat(product,':',group_concat(distinct client)) as "Products Sold" from sales group by salesman,product) derived_table
group by salesman;

 

We have two select statements here - an inner select, and an outer select. 

Inner Statement

The inner select groups the rows in the table by both the salesman and product fields in the same way as the last example. 

From these groups, we take all the distinct client values and perform group_concat on them. Let's say the result of the group_concat is BlueCross, HarperCollins. Then we take the value of product and concat it with the result of the group_concat after a colon (:).If the value of product is Plain, this will result in the value Plain:  BlueCross, HarperCollins. 

The result of the concat function will be in the Products Sold column. 

The output of the inner select statement looks like this.

Outer Statement

The outer select statement operates on the above table - which is given a temporary name derived_table. We group by salesman, and for each group (i.e., each salesman) - we perform group_concat on the Products Sold column. We also specify a separator "|" this time. 

The final output looks like this. 

We can clearly see which salesman has sold which type of product to which client.

We can also perform the same operation grouping by clients instead of salesmen, using the command:

select client, group_concat(`Products Sold` separator '|') as "Products Sold" from 
(select client,concat(salesman,':',group_concat(distinct product)) as "Products Sold" from sales group by client,salesman) derived_table
group by client;

This will result in the final output:

Here, we can see which client has bought which product from which salesman.

Example 6 - Double Nested Group_Concat

In this example, we will see a doubly-nested statement. Here, we want to view both clients and the sum of quantities they ordered - of each product, grouped by salesman. 

For example, we want to know that
Jim sold 115 stocks of Legal paper to BlueCross, 120 stocks of Glossy to Dunmore, etc.

For this, we will need to use the MySQL group_concat function 3 times, which means we will have perform nesting twice.

Command

select salesman,group_concat(client,'->','[',`Products Sold`,']' separator ' | ') as "Products Sold" from 
(
    select salesman, client,group_concat(product,':',quantity) as "Products Sold" from 
    (
        select salesman,product,client,sum(quantity) as quantity from sales group by salesman,product,client
    )tbl1
    group by salesman,client
)tbl2
group by salesman;

Innermost Statement

The inner select groups the rows in the table by salesman, product, and client.

In this:

• Jim's sale of Legal paper to BlueCross.
   
• Jim's sale of Legal to HarperCollins
  .
• Jim's sale of Glossy to BlueCross.
   
• Dwights' sale of Legal to BlueCross.
   
• Dwight's sale of Legal to HarperCollins.
   
• Dwight's sale of Glossy to BlueCross.
   

Will all be in different groups. 
 

From these groups, we calculate the sum of the quantities. This gives us the total quantity of each product sold by each salesman to each client.

The output of the inner select statement looks like this.

(There are a total of 41 rows, we have not shown them all).

The results of this statement are stored in a derived table with the name tbl1.

Middle Statement

The middle select statement operates on the above table. It groups the row by salesman and client - which means that Jim's sales of Legal to Dunmore and Jim's sales of Card to Dunmore will now be in the same group. They were earlier in different groups - but now we aren't grouping by product anymore - so they will be in the same group. 

For each group, we perform group_concat on the product and quantity. This happens in the same way we have seen in the above examples.

The final output looks like this. 

We can see that each salesman's sales to each client are in a single row - and the Products Sold column contains the names of each product sold, along with the quantity. 

These results are stored in a derived table named tbl2.

Outer Statement

The outer select statement operates on the above table. It groups the row by salesman. Now, each salesman will have their own group. For each group, we perform group_concat on the client and the Product Sold column that we formed in the middle select operation. 

We add square brackets and separator | to make the output look cleaner. 

Now, the details of the Product Sold column (which had product and quantities) will be concatenated with the client to whom they were sold.

The final output looks like this. 

 

We can see that each salesman's sales are in a single row. The Products Sold column contains the quantity of each product sold to each client.

How to Concatenate Multiple Rows of Different Columns in a Single Field

Now, let us look at the process of concatenating multiple rows of different columns inside a single field. We can use the concat() function and the group_concat() function to concatenate multiple column values of different rows inside a single field.

Example

Let us now group the product purchased by a particular client and then group them according to the salesman who made the sale. The query for the above idea is given by:

SELECT salesman, GROUP_CONCAT( client separator '| ') as "product : client" 
FROM ( SELECT salesman, CONCAT( product, ':', GROUP_CONCAT(client)) as "client" 
FROM sale GROUP BY product) as sub GROUP BY salesman;

 

Output

Explanation

In the above example, we used two SELECT statements (outer and inner) in the query. The inner SELECT statement will group the table as per the product sold to the client by the customer (product : client). The second SELECT statement will finally group all the rows as per the salesman who sold the product.

Check out this problem - Smallest Distinct Window. 

Frequently Asked Questions

What is group concat in SQL?

GROUP_CONCAT in SQL is a function that concatenates values from multiple rows into a single string, often used with the GROUP BY clause for aggregated results.

What is the separator in MySQL group concat?

The separator in MySQL GROUP_CONCAT is a string that separates the concatenated values. If not specified, the default separator is a comma (,).

What is the alternative to Group_concat in MySQL?

An alternative to GROUP_CONCAT in MySQL is the CONCAT function, which concatenates two or more strings, but it doesn't handle grouping or aggregation.

What type does Group_concat return in MySQL?

GROUP_CONCAT in MySQL returns a string type, representing the concatenated values from the specified columns or expressions.

Conclusion

This blog has explored the group_concat function in MySQL. We have seen what the command does and its syntax. We have seen the function in action in multiple examples, including nested statements. 

We hope you leave this article with a broader knowledge of MySQL and functions in MySQL. We recommend that you explore our different articles on these topics as well, such as:

MySQL Introduction

MySQL Data Types

MySQL Features

MySQL Case Statement

Update Query in MySQL

You can practice questions on various problems on Coding Ninjas Studio, attempt mock tests. You can also go through interview experiences, interview bundle, go along guided paths for preparations, and a lot more!

 

Keep coding, keep reading Ninjas.