Software Technology Tips

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


Related Tags:

MYSQL, group concat

Author: Zainab Ahsan

MySQL

Let us Connect!

iso 9001 QA25 Red Herring STPI D&B Fastest Growing SME 2013 Award zinnov Nasscom

This site uses cookies. We respect your privacy.copyright (c) Mindfire Solutions 2007-2015. Login