MYSQL query for concatenating values (highcharts series data)

Question

Im trying to make query for concatenating values (C) based on type (B) and date (A). If there is no record of B for day I need to put 0 value in concatenated value. Like in example below

+------------+---------+--------+
| A          | B       | C      |
+------------+---------+--------+
| 2017-10-20 | 001     |      1 |
| 2017-10-20 | 002     |      2 |
| 2017-10-21 | 003     |      3 |
| 2017-10-21 | 001     |      4 |
| 2017-10-21 | 002     |      1 |
| 2017-10-22 | 002     |      2 |
| 2017-10-22 | 003     |      2 |
| 2017-10-23 | 001     |      4 |
| 2017-10-23 | 002     |      6 |
| 2017-10-24 | 003     |      1 |
+------------+---------+--------+


+------------+-----------+
| B          | CONCAT    |
+------------+-----------+
| 001        | 1,4,0,4,0 |
| 002        | 2,1,2,6,0 |
| 003        | 0,3,2,0,1 |
+------------+-----------+

But I'm stuck, I need a solution. thank you

The essential problem is to insert 0 if the data for specific date does not exist, in date order

For 001(B) there is 2017-10-20(A) - 1(C), 2017-10-21(A) - 4(C), 2017-10-22(A) - 0(C), 2017-10-23(A) - 4(C), 2017-10-24(A) - 0(C),

SELECT b, 
       Group_concat(c ORDER BY b.a) 
FROM   pom2 a 
       LEFT JOIN (SELECT a 
                  FROM   pom2 
                  GROUP  BY a) b 
              ON a.a = b.a 
GROUP  BY a.b

Returns this without zeros

+-----+------------------------------+
| B   | GROUP_CONCAT(C ORDER BY b.A) |
+-----+------------------------------+
| 001 | 1,4,4                        |
| 002 | 2,1,2,6                      |
| 003 | 3,2,1                        |
+-----+------------------------------+

Hint: GROUP_CONCAT(C ORDER BY A) and GROUP BY B.. plus this question is asked alot on stackoverflow. — Raymond Nijland, Oct 24 '17 at 11:21
@DusanKrstic do you want the query to add `0` values for the dates on which there is no entry? In that case, you will need another table with all the dates and do the `LEFT JOIN`. — Darshan Mehta, Oct 24 '17 at 11:25
SELECT B,GROUP_CONCAT(C ORDER BY A) FROM `pom2` a LEFT JOIN (SELECT A FROM pom2 GROUP BY A) b ON a.A=b.A GROUP BY a.B but response is "Column 'A' in field list is ambiguous" — Dusan Krstic, Oct 24 '17 at 11:49
Possible duplicate of [Can I concatenate multiple MySQL rows into one field?](https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — JochenJung, Oct 24 '17 at 11:50
Put `b.A` inside group_concat and add the result of that query in your question. — Harshil Doshi, Oct 24 '17 at 11:55

score 1 · Accepted Answer · answered Oct 25 '17 at 06:01

Yes it can be done :)

SELECT b, 
       Group_concat(c ORDER BY b.a) 
FROM   (SELECT a.a, 
               a.b, 
               Coalesce(b.c, 0) AS C 
        FROM   (SELECT b.a, 
                       a.b, 
                       0 AS C 
                FROM   pom2 a 
                       CROSS JOIN (SELECT a 
                                   FROM   pom2 
                                   GROUP  BY a) b) a 
               LEFT JOIN pom2 b 
                      ON a.a = b.a 
                         AND a.b = b.b 
        GROUP  BY a.b, 
                  a.a) a 
       LEFT JOIN (SELECT a 
                  FROM   pom2 
                  GROUP  BY a) b 
              ON a.a = b.a 
GROUP  BY a.b

And result is:

+-----+------------------------------+
| b   | Group_concat(c ORDER BY b.a) |
+-----+------------------------------+
| 001 | 1,4,0,4,0                    |
| 002 | 2,1,2,6,0                    |
| 003 | 0,3,2,0,1                    |
+-----+------------------------------+

MYSQL query for concatenating values (highcharts series data)

1 Answers1