MySQL GROUP_CONCAT(): Combine Group Values into One String

GROUP_CONCAT() concatenates non-NULL values from a group into one string. It returns NULL when there are no non-NULL values.

Ordering is not guaranteed unless you specify ORDER BY inside GROUP_CONCAT(). The result length is also limited by the group_concat_max_len system variable (1024 bytes by default).

SELECT GROUP_CONCAT(expression) AS combined_values
FROM table_name;

We will use our sale table with three columns, customer_id,product_id, quantity. Download the table structure with data at the end of this page.

customer_id	product_id	quantity
C2	P4	5
C3	P5	2
C2	P3	3
......	.......	...

Rows data into columns

Let us display all products purchased by customer C3. We will get four rows of data by using this query

SELECT customer_id, product_id, quantity
FROM plus2_sale
WHERE customer_id = 'C3';

CUSTOMER_ID	product_id	quantity
C3	P5	2
C3	P4	2
C3	P5	2
C3	P4	2

We will get all above information in one row by using GROUP_CONCAT .

SELECT customer_id,
       GROUP_CONCAT(product_id, ':', quantity) AS purchases
FROM plus2_sale
WHERE customer_id = 'C3'
GROUP BY customer_id;

CUSTOMER_ID	GROUP_CONCAT(product_id,':',quantity)
C3	P5:2,P4:2,P5:2,P4:2

Show Blob Content

If you are getting BLOB data under GROUP_CONCAT then expand + Options in your phpMyAdmin and check box saying Show BLOB contents .

Using GROUP by along with GROUP_CONCAT

We can modify the above query and get same information for all the customers by using group by.

SELECT customer_id,
       GROUP_CONCAT(product_id, ':', quantity) AS purchases
FROM plus2_sale
GROUP BY customer_id;

CUSTOMER_ID	GROUP_CONCAT(product_id,':',quantity)
C1	P3:5,P3:5
C2	P4:5,P5:2,P3:3,P4:5,P4:6, P5:2,P3:3,P4:6
C3	P4:2,P5:2,P4:2,P5:2

Use of SEPARATOR with group by

SELECT customer_id,
       GROUP_CONCAT(product_id, ':', quantity SEPARATOR '; ') AS purchases
FROM plus2_sale
GROUP BY customer_id;

CUSTOMER_ID	GROUP_CONCAT(product_id, ':', quantity SEPARATOR '; ')
C1	P3:5; P3:5
C2	P4:5; P5:2; P3:3; P4:5; P4:6; P5:2; P3:3; P4:6
C3	P4:2; P5:2; P4:2; P5:2

WITH DISTINCT

In above query we can see customer C2 has purchased a product P4 four times, we can display all matching one time record by using DISTINCT

SELECT customer_id,
       GROUP_CONCAT(DISTINCT product_id, ':', quantity SEPARATOR '; ') AS purchases
FROM plus2_sale
GROUP BY customer_id;

CUSTOMER_ID	GROUP_CONCAT(DISTINCT product_id, ':', quantity SEPARATOR '; ')
C1	P3:5
C2	P4:5; P5:2; P3:3; P4:6
C3	P4:2; P5:2

WITH ORDER BY

We can display from maximum quantity to minimum quantity purchased ( or reverse ) by using ORDER BY

SELECT customer_id,
       GROUP_CONCAT(product_id, '->', quantity ORDER BY quantity DESC) AS purchases
FROM plus2_sale
GROUP BY customer_id;

CUSTOMER_ID	GROUP_CONCAT(product_id, '->', quantity order by quantity desc)
C1	P3->5,P3->5
C2	P4->6,P4->6,P4->5,P4->5,P3->3,P3->3, P5->2,P5->2
C3	P5->2,P4->2,P5->2,P4->2

Using SUM and derived Table

We may require a report showing total of quantity of each product purchased by each customer.
This is a wrong Query and it will show error.

SELECT customer_id,
       GROUP_CONCAT(product_id, SUM(quantity))
FROM plus2_sale
GROUP BY customer_id, product_id;

#1111 - Invalid use of group function

The correct Query is here.

SELECT customer_id,
       GROUP_CONCAT(product_id, ':', quantity_sum SEPARATOR ' ; ') AS purchases
FROM (
    SELECT customer_id, product_id, SUM(quantity) AS quantity_sum
    FROM plus2_sale
    GROUP BY customer_id, product_id
) AS sale_totals
GROUP BY customer_id;

customer_id	GROUP_CONCAT(product_id,':', cast(quantity_sum as char) SEPARATOR ' ; ' )
C1	P3:10
C2	P3:6 ; P4:22 ; P5:4
C3	P5:4 ; P4:4

By using CONVERT() or CHAR()

We can convert integer to string before using inside GROUP_CONCAT

SELECT customer_id,
       GROUP_CONCAT(product_id, ':', CAST(quantity_sum AS CHAR) SEPARATOR ' ; ') AS purchases
FROM (
    SELECT customer_id, product_id, SUM(quantity) AS quantity_sum
    FROM plus2_sale
    GROUP BY customer_id, product_id
) AS sale_totals
GROUP BY customer_id;

Sample table SQL

Use this small InnoDB/utf8mb4 table to reproduce the examples.

CREATE TABLE IF NOT EXISTS plus2_sale (
    customer_id VARCHAR(3) NOT NULL,
    product_id VARCHAR(3) NOT NULL,
    quantity INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample data
INSERT INTO plus2_sale (customer_id, product_id, quantity) VALUES
('C2', 'P4', 5),
('C3', 'P5', 2),
('C2', 'P3', 3),
('C2', 'P5', 2),
('C3', 'P4', 2),
('C1', 'P3', 5),
('C2', 'P4', 6),
('C2', 'P4', 5),
('C3', 'P5', 2),
('C2', 'P3', 3),
('C2', 'P5', 2),
('C3', 'P4', 2),
('C1', 'P3', 5),
('C2', 'P4', 6);

Group By Group by command for more than one column

GROUP BY Multiple-column GROUP BY DISTINCT ORDER BY

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here