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...