Sum of grouped array column elements

17 views Asked by At

How can I get the sum of grouped array column elements?

With this data set:

DROP TABLE A IF EXISTS;
CREATE TABLE A(i INT, ints INT ARRAY);
INSERT INTO A(i, ints) VALUES
  (1, ARRAY[1, 2]),
  (1, ARRAY[3, 4]),
  (2, ARRAY[5, 6]);

I get

SELECT i, ints FROM A;
I | INTS
1 | [1, 2]
1 | [3, 4]
2 | [5, 6]

or

SELECT i, ARRAY_AGG(ints) FROM A GROUP BY i;
I | ARRAY_AGG(INTS)
1 | [[1, 2], [3, 4]]
2 | [[5, 6]]

But I want to get the sums of aggregated array elements:

"SELECT i, SUM(ARRAY_AGG(ints)) FROM A GROUP BY i;"
I | SUM(???)
1 | 10
2 | 11

I tried to get half way by flattening (to then sum) with unnest as shown here. But that gives an error:

SELECT * FROM UNNEST((SELECT ints FROM A));
Error: org.h2.jdbc.JdbcSQLDataException: Scalar subquery contains more than one row;

Also unsuccessfully tried various combinations with TABLE like

SELECT * FROM TABLE(V INT = SELECT * FROM UNNEST((SELECT ints FROM A)));

Very thankful for any solution or pointer in some direction...

0

There are 0 answers