CODEWITHSUNDEEP
clickhouse
Enric Calabuig
Enric Calabuig

Reputation: 530

How to sum arrays element by element after group by in clickhouse

I am trying to add an array column element by element after a group by another column.

Having the table A below: 

id    units
 1    [1,1,1]
 2    [3,0,0]
 1    [5,3,7]
 3    [2,5,2]
 2    [3,2,6]

I would like to query something like: 

select id, sum(units) from A group by id

And get the following result:

id    units
 1    [6,4,8]
 2    [6,2,6]
 3    [2,5,2]

Where the units arrays in rows with the same id get added element by element.

Upvotes: 1

Views: 4369

Answers (1)

vladimir
vladimir

Reputation: 15178

Try this query:

SELECT id, sumForEach(units) units
FROM (
  /* emulate dataset */
  SELECT data.1 id, data.2 units
  FROM (
    SELECT arrayJoin([(1, [1,1,1]), (2, [3,0,0]), (1, [5,3,7]), (3, [2,5,2]), (2, [3,2,6])]) data))
GROUP BY id

/* Result
┌─id─┬─units───┐
│  1 │ [6,4,8] │
│  2 │ [6,2,6] │
│  3 │ [2,5,2] │
└────┴─────────┘
*/

Upvotes: 8

Related Questions