Concatenation using a mysql variable

Question

The problem in my query particularly in this piece:

@a:= concat(@a, ',', B.call_account_id) AS paid_account_id

Here is the whole query:

SELECT operator_id, paid_account_ids,  SUM( goods_count * price ) AS sales_volume, count(*) AS sales_cnt
FROM (
    SELECT B.operator_id, @a:= concat(@a, ',', B.call_account_id) AS paid_account_ids, B.call_time, A.goods_count, A.price, UNIX_TIMESTAMP( A.completion_date ) AS paid_ts
    FROM call_module_data B
    INNER JOIN ak_accounts A ON ( A.account_id = B.call_account_id AND A.goods_count >=1 )
    WHERE B.call_status IN (1,7) AND A.status_id = 5
    AND operator_id IN ( $op_ids )
    $and_str_accounts
    GROUP BY A.account_id
    HAVING call_time < (paid_ts + $time_shift)
    ) AS T
GROUP BY operator_id";

So the expression mentioned above should produce the concatenated string of account ids (e.g 3341,4355,4433...). But I got NULL instead of desired string. Please help to resolve. Thanks in advance.

You have a quote and a semicolon at the end there, that's why it won't work. — Eric Leschinski
– Eric Leschinski, Commented Jan 8, 2014 at 7:28

Saharsh Shah · Accepted Answer · 2014-01-08 07:58:51Z

1

Use GROUP_CONCAT() function instead of String concatenation

Change

@a:= concat(@a, ',', B.call_account_id) AS paid_account_id

above string to

GROUP_CONCAT(B.call_account_id) AS paid_account_ids

Final Answer:

SELECT operator_id, GROUP_CONCAT(paid_account_ids) AS paid_account_ids, 
       SUM(goods_count * price) AS sales_volume, COUNT(*) AS sales_cnt
FROM (SELECT B.operator_id, GROUP_CONCAT(B.call_account_id) AS paid_account_ids, 
             B.call_time, A.goods_count, A.price, 
             UNIX_TIMESTAMP(A.completion_date) AS paid_ts
      FROM call_module_data B
      INNER JOIN ak_accounts A ON A.account_id = B.call_account_id AND A.goods_count >=1
      WHERE B.call_status IN (1,7) AND A.status_id = 5 AND operator_id IN ($op_ids)
            $and_str_accounts
      GROUP BY A.account_id HAVING call_time < (paid_ts + $time_shift)
     ) AS T
GROUP BY operator_id;

edited Jan 8, 2014 at 7:58

answered Jan 8, 2014 at 7:30

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sergey Onishchenko Over a year ago

It looks like it should work but I get only first account id instead of concatenated string !?

Collectives™ on Stack Overflow

Concatenation using a mysql variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related