Strange GROUP_CONCAT behaviour in MySQL

Question

I ran into a really strange problem today when using the MySQL function GROUP_CONCAT:

I have the following query:

SELECT SUM(total) FROM order WHERE customer_id='X' AND order_status_id IN ((SELECT GROUP_CONCAT(order_status_id SEPARATOR ',') FROM order_status WHERE profit='1'))

but that returns NULLL, however:

SELECT SUM(total) FROM order WHERE customer_id='X' AND order_status_id IN (1,2,3,4,5,6,7,8)

this works as well as the first query to concat the status id's, grouped however they return NULL as total

Related: http://stackoverflow.com/questions/4155873/find-in-set-vs-in — gen_Eric, Aug 07 '12 at 14:04

score 3 · Accepted Answer · answered Aug 07 '12 at 14:04

GROUP_CONCAT() returns a string, which is a single value. The IN() clause, although it accepts a comma-separated list of values, won't take just any string you give it and then parse out the individual values.

It treats the result of the GROUP_CONCAT() as a single string unit, which could be a member of many other strings in a comma-separated list, but no rows match order_status_id = '1,3,5,6', which is what the RDBMS ultimately sees.

If you want to use a query for that IN() clause, use a subquery. The IN () clause knows how to deal with the rowset returned by the subquery and will treat it appropriately.

SELECT
  SUM(total) 
FROM order 
WHERE 
  customer_id='X' 
  AND order_status_id IN (
    SELECT order_status_id FROM order_status WHERE profit = '1'
  );

score 1 · Answer 2 · answered Aug 07 '12 at 14:08

About your question. Try to use FIND_IN_SET function -

SELECT
  SUM(total)
FROM
  `order`
WHERE
  customer_id='X' AND
  FIND_IN_SET(
    order_status_id,
    (SELECT GROUP_CONCAT(order_status_id) FROM order_status WHERE profit='1')
  )

I'd suggest you to use JOIN clause. For example -

SELECT o.* FROM `order` o
  JOIN order_status os
    ON o.order_status_id = os.order_status_id
WHERE o.customer_id='X' AND os.profit = 1

...then add aggregate functions you need.

Strange GROUP_CONCAT behaviour in MySQL

2 Answers2