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 By
Group by command for more than one column →