Sum of repeated numeric columns (array) in Bigquery

Question

How can I get the sum of a repeated numeric column (array) in BigQuery ?

For example, from the following array:

SELECT [4, 5, 9] as repeated_numeric
UNION ALL SELECT [2,3];

Row	repeated_numeric
1	[4, 5, 9]
2	[2,3]

I am looking for this result:

Row	sum
1	18
2	5

The length of my arrays is not fixed.

Mikhail Berlyant · Accepted Answer · 2022-08-24 13:44:24Z

1

Consider below simple approach

select repeated_numeric, 
  (select sum(num) from t.repeated_numeric num) total
from your_table t

if applied to sample data in your question - output is

answered Aug 24, 2022 at 13:44

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1