GROUP_CONCAT to concatenated group data to a string by adding distinct, order by SUM to display as single column

Returns concatenated non-NULL values string from a group. Returns null if there is no non-null value.

SELECT GROUP_CONCAT(exp);

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) FROM  `plus2_sale` WHERE customer_id='C3'

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_contact 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 ) 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 '; ') 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 '; ') 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) 
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 ' ; ' ) FROM 
(SELECT customer_id,product_id,sum(quantity) as quantity_sum from plus2_sale group by customer_id,product_id) a
 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 ' ; ' )
 FROM (SELECT customer_id,product_id,sum(quantity) as quantity_sum from plus2_sale group by customer_id,product_id) a
 group by customer_id

MySQL dump for plus2_sale table

CREATE TABLE IF NOT EXISTS `plus2_sale` (
  `customer_id` varchar(3) NOT NULL,
  `product_id` varchar(3) NOT NULL,
  `quantity` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `plus2_sale`
--

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_CONCAT()