mysql group_concat (sum)

Question

tabel:

name    time                data

AAA    2021-10-1 13:05:00   11
AAA    2021-10-1 13:05:00   20
AAA    2021-10-1 14:10:00   35
BBB    2021-10-1 13:05:00   20
BBB    2021-10-1 13:05:00   20
BBB    2021-10-1 14:10:00   10
CCC    2021-10-1 14:10:00   8

i'm tried :

select name,group_concat(data) from wip where time between '2021-10-26 00:00:00' and '2021-10-26 23:59:59' GROUP BY name

result:

name   data
AAA   11,20,35
BBB   20,20,10
CCC   8

want sum(data) group by hour(time) result like this:

name   data
AAA    [31,35]
BBB    [40,10]
CCC    [ 0,18]

What value is there in displaying 0 in the output? Perhaps showing what IS using group_concat is appropriate. — P.Salmon, Dec 09 '21 at 16:24
select name,group_concat(sum(data)) from wip where time between '2021-10-26 00:00:00' and '2021-10-26 23:59:59' GROUP BY name — YH.CHENG, Dec 14 '21 at 03:46
Nested selects -- inner: SUM(data) GROUP BY time; the outer one using GROUP_CONCAT. — Rick James, Dec 19 '21 at 22:35

score 0 · Accepted Answer · answered Dec 19 '21 at 22:38

It takes 2 steps:

SELECT name, GROUP_CONCAT(sum_data)
    FROM ( SELECT name, time, SUM(data) AS sum_data
              FROM tbl GROUP BY name, time ) AS x

(And I don't see "pivoting" as being relevant.)

If you need the brackets, use CONCAT("[", GROUP_CONCAT(sum_data), "]")

mysql group_concat (sum)

1 Answers1