Software Technology Tips

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
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:
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:
Chinese, Khmer, Kuy, Lao

Related Tags:

MYSQL, group concat

Author: Zainab Ahsan


Let us Connect!

Awards and Achievements

Red Herringcolor DeloitteFast50 DB ZInnov1   Nascome  DB Stpi

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