CODEWITHSUNDEEP
mysql
xjshiya
xjshiya

Reputation: 925

MySQL group_concat and concat

I have these data on MySQL table cart.

enter image description here

I would like to run a query that would output like this:

Advance Technique Conditioner (2), Advance Technique Gel (1)

So I was doing a group_concat query and was trying queries to output my desired output. This is the query that outputted the nearest to the desired:

select concat(group_concat(product separator ', '), group_concat(quantity separator ', ')) as 'prod' from cart where debt_no='0000001'

But as expected, it outputted like this : Advance Technique Conditioner, Advance Technique Gel2, 1

How can I get the desired output?

Upvotes: 2

Views: 8155

Answers (3)

John Woo
John Woo

Reputation: 263683

CONCAT() should be inside GROUP_CONCAT()

GROUP_CONCAT(CONCAT(product, ' (', CAST(quantity AS CHAR(15)), ')') SEPARATOR ', ')

remember that the default length of GROUP_CONCAT() is 1024. So if you want to change the limit execute the following line,

SET [GLOBAL | SESSION] group_concat_max_len = val; -- val is the new length

Upvotes: 8

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You need to do the concat() first, then the group_concat():

select group_concat(concat(product, ' (', quantity, ')') separator ', ') as prod
from cart where debt_no='0000001'

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191729

SELECT GROUP_CONCAT(CONCAT(product, ' (', quantity, ')') SEPARATOR ', ')

Upvotes: 2

Related Questions