GROUP_CONCAT()

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_idproduct_idquantity
C2P45
C3P52
C2P33
................

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_IDproduct_idquantity
C3P52
C3P42
C3P52
C3P42
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_IDgroup_concat(product_id,':',quantity)
C3P5: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_IDgroup_concat(product_id,':',quantity)
C1P3:5,P3:5
C2P4:5,P5:2,P3:3,P4:5,P4:6,
P5:2,P3:3,P4:6
C3P4: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_IDgroup_concat(product_id, ':', quantity SEPARATOR '; ')
C1P3:5; P3:5
C2P4:5; P5:2; P3:3; P4:5; P4:6; P5:2; P3:3; P4:6
C3P4: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_IDgroup_concat(DISTINCT product_id, ':', quantity SEPARATOR '; ')
C1P3:5
C2P4:5; P5:2; P3:3; P4:6
C3P4: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_IDgroup_concat(product_id, '->',
quantity order by quantity desc)
C1P3->5,P3->5
C2P4->6,P4->6,P4->5,P4->5,P3->3,P3->3,
P5->2,P5->2
C3P5->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_idGROUP_CONCAT(product_id,':',
cast(quantity_sum as char) SEPARATOR ' ; ' )
C1P3:10
C2P3:6 ; P4:22 ; P5:4
C3P5: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);

Visitors Rating
Your Rating




Google+

Post Comment This is for short comments only. Use the forum for more discussions.








HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2018 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer