Using GROUP_CONCAT IN MYSQL

USE OF GROUP_CONCAT IN MYSQL

It is a useful extension on Group by clause

It returns concatenated non null values from a group

Syntax:  

GROUP_CONCAT(column_name) [ default separator is comma]
GROUP_CONCAT(column_name SEPARATOR '_') [ if you want to separate the values with an underscore.]

Suppose you have a requirement like below:

You have a table order { order_id, customer_id, totalqty, totalamount}
and another table payment { order_id, payment_term_id,amount_paid}
 
Where you can register multiple payments for an order either with same payment_term_id OR different

say available values for payment_term_id are { Cash, Cheque , Draft }

Requirement: You need to fetch total amount paid per order and in the mean time you need to
show all the payment_term_id used for registering payment for an order

Query 1:

SELECT SUM(amount_paid) AS total, payment_term_id AS payment FROM order ord INNER JOIN payment pay
ON ord.order_id = pay.payment_id GROUP BY ord.order_id

Query 2:

SELECT SUM(amount_paid) AS total, GROUP_CONCAT(payment_term_id SEPARATOR ',') AS payment FROM order ord INNER JOIN payment pay
ON ord.order_id = pay.payment_id GROUP BY ord.order_id

  Query 1 will return only one payment term id

but query 2 will return all payment term id used for paying an order separated with comma

Like if four payments have been made for an order with these payment terms Cash,Draft,Cash,Cheque,
query 2 will return

total payment

550 Cash,Draft,Cash,Cheque
NOTES:
1.DISTINCT can also be used in GROUP_CONCAT to get distinct values
2.ORDER BY Clause can also be used inside GROUP_CONCAT
2.GROUP_CONCAT ignores NULL values
3.MAX limit of GROUP_CONCAT is 1024 which can be manipulated for a session
by using
SET SESSION group_concat_max_len=1024 * 8 [ If you want to increase the size 8 times]
4.Default Separator is comma
5.To use another separator use SEPARATOR keyword as described above.
GROUP_CONCAT(payment_term_id SEPARATOR '@')
6.To eliminate the separator altogether, specify SEPARATOR ''.
GROUP_CONCAT(payment_term_id SEPARATOR '')

Another example

mysql> SELECT Language FROM CountryLanguage WHERE CountryCode = 'THA';
 
It outputs:
 
Language
Chinese
Khmer
Kuy
Lao
 
To concatenate the values into a single string, you query:
 
mysql> SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';
 
Then the output will be:
 
Languages
Chinese, Khmer, Kuy, Lao